Search code examples
sqloracleoracle-sqldeveloper

Dense Rank grouping by IDs

I am having trouble getting my DENSE_RANK() function in Oracle to work how I would like. First, my dataset:

ID      DATE
1234    01-OCT-2020
1234    01-OCT-2021
1234    01-OCT-2022
2345    01-APR-2020
2345    01-APR-2021
2345    01-APR-2022

I am trying to use the dense rank function to return results with a sequence number based on the DATE field, and grouping by ID. How I want the data to return:

ID      DATE           SEQ
1234    01-OCT-2020    1
1234    01-OCT-2021    2
1234    01-OCT-2022    3
2345    01-APR-2020    1
2345    01-APR-2021    2
2345    01-APR-2022    3

The query I have so far:

 SELECT ID, DATE, DENSE_RANK() Over (order by ID, DATE asc) as SEQ

However, this returns incorrectly as the sequence number will go to 6 (Like its disregarding my intentions to sequence based on the DATE field within a certain ID). If anyone has any insights into how to make this work it would be very much appreciated!

Solution

  • You want row_number():

    select id, date, row_number() over (partition by id order by date) as seq

    You could actually use dense_rank() as well, if you want duplicates to have the same idea. The key idea is partition by.