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

​x
 
ID      DATE1234    01-OCT-20201234    01-OCT-20211234    01-OCT-20222345    01-APR-20202345    01-APR-20212345    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

 
ID      DATE           SEQ1234    01-OCT-2020    11234    01-OCT-2021    21234    01-OCT-2022    32345    01-APR-2020    12345    01-APR-2021    22345    01-APR-2022    3​

The query I have so far:

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

 
 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!

Answer

You want row_number():

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

 
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.

Advertisement

Answer