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?
@OMG_Ponies already pointed it out: The window function dense_rank() is what you need - or maybe rank(). The UPDATE could look like this:
Test case:
CREATE TEMP TABLE tbl (
id int
, dollars int
, dollars_rank int
, points int
, points_rank int
);
INSERT INTO tbl VALUES
(1, 20, 1, 35, 1)
,(2, 18, 2, 30, 3)
,(3, 10, 3, 33, 2)
,(4, 10, 3, 33, 2); -- I put a dupe row in to demonstrate ties.
UPDATE statement:
UPDATE tbl
SET dollars_rank = r.d_rnk
, points_rank = r.p_rnk
FROM (
SELECT id
, dense_rank() OVER (ORDER BY dollars DESC) AS d_rnk
, dense_rank() OVER (ORDER BY points DESC) AS p_rnk
FROM tbl
) r
WHERE tbl.id = r.id
You need PostgreSQL 8.4 or later for window functions.
