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 |
I think you should use DENSE_RANK and Distinct. So, the query should be look like
--Without Duplicate Dates
Select Distinct YouDATE,
DENSE_RANK() OVER(ORDER BY YourDateColumn) MyRANK
FROM YourTable;
--With Duplicate Dates
Select YouDATE,
DENSE_RANK() OVER(ORDER BY YourDateColumn) MyRANK
FROM YourTable;
Note: This code is written in SQL SERVER. Please check and let me know.
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;