CODEWITHSUNDEEP
sqlsql-server-2005
Karan
Karan

Reputation: 3328

SQL Server sort column based on the same column itself

I have a db table containing a column display_order. The data looks like this:

2 
4 
7 
10 
12

I want to update the same db column and it should look like this:

1
2
3
4
5

Please suggest some easy SQL code.

Upvotes: 0

Views: 191

Answers (3)

IvanH
IvanH

Reputation: 5139

Without CTE (but needs some key in the table)

declare @tbl table(id int primary key identity(1,1),Value int)
insert @tbl values(2)
insert @tbl values(5)
insert @tbl values(3)

select * from @tbl 

select *, ROW_NUMBER() over(order by Value) from @tbl order by id

update @tbl set Value = result from @tbl tbl 
inner join (select id, ROW_NUMBER() over(order by Value) result from @tbl ) hlp    on tbl.id =hlp.ids

select * from @tbl

Upvotes: 0

marc_s
marc_s

Reputation: 754258

Expanding on AdaTheDev's idea - using a CTE (Common Table Expression) makes it really easy to see how to use the actual UPDATE to update your table. I'm using a table variable @work here to simulate your existing table - just replace my table variable with your own table name:

DECLARE @work TABLE (display_order INT)

INSERT INTO @work VALUES(2)
INSERT INTO @work VALUES(4)
INSERT INTO @work VALUES(7)
INSERT INTO @work VALUES(10)
INSERT INTO @work VALUES(12)

SELECT * FROM @work

;WITH UpdateTable AS
(
    SELECT 
        display_order, new_order = ROW_NUMBER() OVER (ORDER BY display_order) 
    FROM @work
)
UPDATE @work
SET display_order = u.new_order
FROM @work w 
INNER JOIN UpdateTable u ON w.display_order = u.display_order

SELECT * FROM @work

Upvotes: 0

AdaTheDev
AdaTheDev

Reputation: 147224

Have a look into ROW_NUMBER(), this will help you here.

e.g. demo that won't update your data, but will show you the current order and the new order based on ROW_NUMBER

SELECT display_order AS CurrentDisplayOrder, 
    ROW_NUMBER() OVER (ORDER BY display_order) AS NewDisplayOrder
FROM YourTable
ORDER BY display_order

If that produces what you'd expect, then you can just switch it into an UPDATE statement.

Upvotes: 4

Related Questions