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
Advertisement
Answer
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