How can I write a window function using something like row_number to increment only on discrete values?

Question

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 - Slava Ukraini, Commented Mar 4, 2019 at 22:03

user1822user1822 · Accepted Answer · 2019-03-04 21:08:55Z

3

You want dense_rank() instead of row_number()

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

answered Mar 4, 2019 at 21:08

user1822

Add a comment |

1 Answer 1