Unlike Oracle and Microsoft SQL Server 2005, PostgreSQL offers no built-in function to dynamically rank rows. However, one can use the MSDN article, How to dynamically number rows in a SELECT Transact-SQL statement, as a basis for developing techniques that do work in PostgreSQL. This article assumes that there exists a table named players, containing two columns, player_id and score.
The first technique is the simplest to understand, but requires the use of a temporary sequencer. If sequencer functionality is not available in your database, then this technique cannot be utilitized:
CREATE TEMPORARY SEQUENCE rank_seq;
SELECT player_id, score, nextval('rank_seq') AS rank FROM
(SELECT * FROM players ORDER BY score DESC) as p1
ORDER BY rank
The second technique uses a subselect statement to rank its result. This can be computationally expense because of the COUNT operation within the subselect statement:
SELECT
player_id, score,
(SELECT COUNT(*) FROM players p2
WHERE p2.score > p1.score) + 1 AS rank
FROM
players p1
ORDER BY score DESC
The third and final technique uses a self-join operation. It is ideal for environments on lightweight databases where the subselect statement is available. Unfortunately, this technique is also the least intuitive of the three. I recommend running this query piecewise in a query browser to understand how the individual components fit together:
SELECT
p1.player_id, p1.score, COUNT(p2.player_id) AS rank
FROM
players p1, players p2
WHERE
p2.score >= p1.score
GROUP BY
p1.player_id, p1.score
ORDER BY rank
