PostgreSQL: getting ordinal rank (row index? ) efficiently

Question

You have a table like so:

id dollars dollars_rank points points_rank
1  20      1            35     1
2  18      2            30     3
3  10      3            33     2

I want a query that updates the table's rank columns (dollars_rank and points_rank) to set the rank for the given ID, which is just the row's index for that ID sorted by the relevant column in a descending order. How best to do this in PostgreSQL?

Answer 1

The window function dense_rank() is what you need - or maybe rank(). The UPDATE could look like this:

UPDATE tbl
SET    dollars_rank = r.d_rnk
     , points_rank  = r.p_rnk
FROM  (
    SELECT id
         , dense_rank() OVER (ORDER BY dollars DESC NULLS LAST) AS d_rnk
         , dense_rank() OVER (ORDER BY points  DESC NULLS LAST) AS p_rnk
    FROM   tbl
    ) r
WHERE  tbl.id = r.id;

fiddle

NULLS LAST is only relevant if the involved columns can be NULL:

• Sort by column ASC, but NULL values first?