For data:
20
50
50
60
70
If I use RANK I get
1
2
2
4
5
if I use DENSE_RANK I get
1
2
2
3
4
I need for my application this:
1
3
3
4
5
I think you want:
rank() over(order by val) + count(*) over(partition by val) - 1
Actually this would be simpler phrased with just a window count:
count(*) over(order by val)
select val, count(*) over(order by val) rn
from (values (20), (50), (50), (60), (70)) as t(val)
order by val
val | rn --: | -: 20 | 1 50 | 3 50 | 3 60 | 4 70 | 5