Search code examples
sqlpostgresqlwindow-functions

How to rank rows considering ties?

How to show numbers 1 1 3 4 5 5 7... in PostgreSQL query

Example:

create table test(name,sum_all)as values
 ('a',100)
,('b',95)
,('c',100)
,('d',75)
,('e',55);

Desired results

name sum_all order_number
a 100 1
c 100 1
b 95 3
d 75 4
e 55 5

Where the "order_number" column runs the normal number sequence, but if "sum_all" has the same value, the number runs the same and skips running the number to the next sequence.

Solution

  • I think you are looking for the rank() window function:

    SELECT name, sum_all,
       rank() OVER (ORDER BY sum_all DESC) AS order_number
FROM tab;