Skip to content
Advertisement

SQL RANK but higher if equal?

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)

Demo on DB Fiddle:

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
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement