Resequence Puzzle - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Resequence Puzzle

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2005-12-27 : 22:02:56

I've solved this with a WHILE loop.

I was wondering if there was an elegant non-WHILE solution.

A column contains integers, all positive, non-zero.

Resequence the integers so the minimimum is 1, the next minimum is 2 and so on.

Just so you have some names to work with...

CREATE TABLE MyTable (MyInt INT NOT NULL)

Source data might be... 5, 9, 15, 20

Result data should be... 1, 2, 3, 4

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-27 : 22:26:14

I don't know how you define "elegant" but it is non-WHILE at least...


use pubs
set nocount on
go
CREATE TABLE MyTable (MyInt INT NOT NULL)
go
insert myTable(myint)
select 5 union
select 9 union
select 15 union
select 20

update c set 
	c.myInt = b.seq
from	myTable c
join	(
	select myint, seq = (select count(*) from myTable where myint <= a.myint)
	from	myTable a
	) b
		on b.myint = c.myint

select * from myTable

go
drop table myTable

or how about this:


declare @rc int
select @rc = count(*) from myTable
drop table myTable
set rowcount @rc
select number myint into myTable from numbers where number > 0 order by number
set rowcount 0

Be One with the Optimizer
TG

SamC
White Water Yakist

3467 Posts

Posted - 2005-12-27 : 23:29:49

TG, I like the 1st solution. The 2nd makes my head hurt

Thanks!

SamC
White Water Yakist

3467 Posts

Posted - 2005-12-27 : 23:59:43

Soooooo much better than a WHILE loop...

UPDATE	A
	SET	Qnum	= B.NewModuleSequence
	FROM	#Answers A
	INNER JOIN (
		SELECT	AA.ModuleSequence, COUNT(*) AS NewModuleSequence
			FROM	(
				SELECT	DISTINCT ModuleSequence
					FROM	#Answers
			) AA
			LEFT OUTER JOIN (
				SELECT	DISTINCT ModuleSequence
					FROM	#Answers
			) BB	ON	BB.ModuleSequence <= AA.ModuleSequence
			GROUP BY AA.ModuleSequence
	) B ON B.ModuleSequence = A.ModuleSequence

Thanks

again !

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-28 : 01:16:04

Do you really want to update or display the sequence number?
How about having a view that does resequencing without updating the orginal values?

Create View yourView
as
Select (Select count(*) from mytable where myint<=T.myint) as Sno,myint from mytable T
Go

Select Sno as myint from yourView

Madhivanan

Failing to plan is Planning to fail

SamC
White Water Yakist

3467 Posts

Posted - 2005-12-28 : 08:21:28

quote:
Originally posted by madhivanan

Do you really want to update or display the sequence number?
How about having a view that does resequencing without updating the orginal values?

Create View yourView
as
Select (Select count(*) from mytable where myint<=T.myint) as Sno,myint from mytable T

I omitted the point that many numbers will be duplicates. Taking the duplicates into account, your VIEW proposal would be as complicated as the INNER JOIN solution once it's revised.

The source data with duplicates might be

2, 5, 5, 8, 9

The result should be

1, 2, 2, 3, 4

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-28 : 08:28:03

Then use Distinct count



CREATE TABLE MyTable (MyInt INT NOT NULL)
go
insert myTable(myint)
select 2 union all
select 5 union all  
select 5 union all
select 8 union all
select 9

Select (Select count(distinct myint) from mytable where myint<=T.myint) as Sno from mytable T

drop table MyTable

Madhivanan

Failing to plan is Planning to fail

SamC
White Water Yakist

3467 Posts

Posted - 2005-12-28 : 11:59:14

quote:
Originally posted by madhivanan

Select (Select count(distinct myint) from mytable where myint<=T.myint) as Sno from mytable T

I'd had a bad execution plan experience as a young child coding a subquery in a select list. Seemed the execution plan would execute the subquery once on every row generated. This trauma shaped my thoughts about how SQL should be written.

I'm surprised that the query above has an excellent execution plan, probably better than a comparable plan using JOINS.

Go figure.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-28 : 12:56:58

I'd had the same traumatic childhood subquery experience. I noticed the plans had become much better somewhere along the way but I still sleep with one eye open when a subquery in a select list is in the next room.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources