0

The first column of a table is the date.

I need to sort the rest of the data while actually updating the table according to date (descending). I can't use UPDATE and ORDER BY together.

I tried using something similar to this post: 

UPDATE Test 
SET Number = rowNumber 
FROM Test
INNER JOIN 
    (SELECT ID, row_number() OVER (ORDER BY ID DESC) as rowNumber
     FROM Test) drRowNumbers ON drRowNumbers.ID = Test.ID

in

SQL Server: UPDATE a table by using ORDER BY

But I cannot mix row_number and date.

Any help will be appreciated.

Community
  • 1
  • 1
Fariya Farhad
  • 33
  • 2
  • 9
  • couldn't you just run a second query to sort and use ROW_NUMBER() OVER (ORDER BY date_column) in the update? – Dan Nov 30 '16 at 20:27
  • I can't put the date_column inside ROW_Number. I get the message "Operand type clash: bigint is incompatible with date" ROW_Number returns bigint – Fariya Farhad Nov 30 '16 at 20:31

1 Answers1

0

Use a CTE:

Check it here: http://rextester.com/NGTM78965

create table #t1 (id int, d datetime, rown int);
insert into #t1 values (1,'2016-05-01',0);
insert into #t1 values (2,'2016-05-04',0);
insert into #t1 values (3,'2016-06-01',0);
insert into #t1 values (4,'2016-04-03',0);
insert into #t1 values (5,'2016-05-12',0);
insert into #t1 values (6,'2016-08-01',0);
insert into #t1 values (7,'2016-05-15',0);
insert into #t1 values (8,'2016-05-25',0);

with cte (id, d, r) as
(
select id, d, row_number() over (order by d) r 
from #t1
)
update #t1
set #t1.rown = cte.r
from cte inner join #t1 on #t1.id = cte.id;

select * from #t1 order by d
McNets
  • 10,352
  • 3
  • 32
  • 61
  • Hi. I ran this one. The table that is created is still not sorted. Only the display is. – Fariya Farhad Nov 30 '16 at 21:16
  • I've created a table with rown=0 in all rows, then after the update, I show you the actual values, rown has been modified. `select * from #t1 order by d` – McNets Nov 30 '16 at 21:21
  • My bad. I didn't quite understand it before. But this method worked for me! Thanks so much! – Fariya Farhad Dec 01 '16 at 14:49