Is there an elegant way? - 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 2005 Forums

Transact-SQL (2005)

Is there an elegant way?

Author

Topic

tonyr1971
Starting Member

5 Posts

Posted - 2009-04-29 : 06:31:29

Hi All,

If I have a table with the following data, I want to change the sequence of any item, is there an elegant SQL way of doing this?

For example:

My table is:

ObjectID nvarchar(8)
Sequence int

Data is:

Fridge1 1
Fridge2 2
Fridge3 4
Fridge4 3
Fridge5 5

If I want to move Fridge5 to the top, as in:

Fridge1 2
Fridge2 3
Fridge3 5
Fridge4 4
Fridge5 1

I have to update all the other Sequence in the table for the correct order. I know I could setup a temp table and re-order the sequence.

Is there a better way?

Cheers

Tony

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-29 : 06:47:26

BEGIN TRAN

UPDATE Table1
SET Sequence = 0
WHERE ObjectID = 'Fridge5'

UPDATE f
SET Sequence = recID
FROM (
SELECT Sequence, ROW_NUMBER() OVER (ORDER BY Sequence) AS recID
FROM Table1
) AS f

COMMIT TRAN

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-29 : 06:57:09

[code]UPDATE f
SET Sequence = recID
FROM (
SELECT Sequence,
ROW_NUMBER() OVER (ORDER BY CASE WHEN ObjectID = 'Fridge5' THEN 0 ELSE 1 END, Sequence) AS recID
FROM Table1
) AS f[/code]

E 12°55'05.63"
N 56°04'39.26"

tonyr1971
Starting Member

5 Posts

Posted - 2009-04-29 : 07:02:48

Wow, superb!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources