0

Say I want to do something like:

select 
     my_date,
     row_number() over(partition by my_date order by my_date asc)
from
     my_table

I want the row numbers to come out as:

2018-10-01,1
2018-10-01,1
2018-10-02,2
2018-10-03,3

Not quite sure how to do this. With the current window function, it does:

2018-10-01,1
2018-10-01,2
2018-10-02,1
2018-10-03,1
  • If you partition by mydate, the count will restart for every new date. I believe the answer by @a_horse_with_no_name is what you are looking for. – Lennart Mar 4 '19 at 22:03
3

You want dense_rank() instead of row_number()

select my_date,
       dense_rank() over(order by my_date asc)
from my_table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.