sqloracle-databasesequential-number

SQL to add sequential numbers to rows by date

I'm trying to recreate a table that was made in MS Access. I need to add a column called 'Week' with a sequential number in descending order by date.

Example of my dataset:

Value,Date
25.4,9/12/2018
33.6,9/12/2018
34.4,9/12/2018
23.4,9/11/2018
243.4,9/11/2018
322.4,9/11/2018
252.4,9/11/2018
22.4,9/10/2018
112.4,9/10/2018
422.4,9/10/2018
232.4,9/10/2018

Example of output I'm after:

Week,Value,Date
1,25.4,9/12/2018
1,33.6,9/12/2018
1,34.4,9/12/2018
2,23.4,9/11/2018
2,243.4,9/11/2018
2,322.4,9/11/2018
2,252.4,9/11/2018
3,22.4,9/10/2018
3,112.4,9/10/2018
3,422.4,9/10/2018
3,232.4,9/10/2018
Solution

  • Sounds like dense_rank() might help you.

    SELECT dense_rank() OVER (ORDER BY date DESC) week,
       value,
       date
       FROM elbat;