This seems simple enough but my brain isn't working and thought I'd ask the group. I want to rank the dates in a a fashion similar to the table below. There are other fields in this table but if the date repeats, then the rank can also repeat for that same date as shown below for 12/31 and 1/5
Date | Rank |
---|---|
12/31/2020 | 1 |
12/31/2020 | 1 |
1/1/2021 | 2 |
1/2/2021 | 3 |
1/2/2021 | 3 |
1/3/2021 | 4 |
1/4/2021 | 5 |
1/5/2021 | 6 |
1/5/2021 | 6 |
1/7/2021 | 7 |
1/10/2021 | 8 |
You seem to want dense_rank()
:
select t.*,
dense_rank() over (order by date) as rank
from t;
This assumes that the issue with 2021-01-02 is a typo in the question.
If there are time components on the "date"s, then convert to a date for the ordering:
select t.*,
dense_rank() over (order by convert(date, date)) as rank
from t;