0

How can I add a sequential row number to a query that is using order by?

Let say I have a request in this form :

SELECT row_number() over(), data
FROM myTable
ORDER BY data

This will produce the desired result as rows are ordered by "data", but the row numbers are also ordered by data. I understand this is normal as my row number is generated before the order by, but how can I generate this row number after the order by?

I did try to use a subquery like this :

SELECT row_number() over(ORDER BY data), *
FROM
(  
   SELECT data 
   FROM myTable
   ORDER BY data
) As t1

As shown here, but DB2 doesn't seem to support this syntax SELECT ..., * FROM

Thanks !

Community
  • 1
  • 1
Oli_G
  • 460
  • 3
  • 17

1 Answers1

2

You also need to use alaias name before '*'

SELECT row_number() over(ORDER BY data), t1.*
FROM
(  
   SELECT data 
   FROM myTable
   ORDER BY data
) As t1

You don't need a subquery to do this,

   SELECT data , row_number() over(ORDER BY data) as rn
   FROM myTable
   ORDER BY data
Santhosh
  • 1,771
  • 1
  • 15
  • 25
  • It works with the t1.*. I oversimplified my example too much : I use a rand() to generate a column in the query on wich I do my order by. placing this column in the over() function did not work in a single query. Anyway your answer get me going so thanks a lot ! – Oli_G Nov 26 '13 at 14:23