Skip to content
GreenSnippets

RANK, DENSE_RANK and ROW_NUMBER

Understand how the three ranking window functions treat ties, with one query that shows all three.

SQL
SELECT
  name,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_number,
  RANK()       OVER (ORDER BY score DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM players
ORDER BY score DESC, name;

Example / expected output

Output
-- name   score  row_number  rank  dense_rank
-- Ada      90        1        1       1
-- Lin      90        2        1       1
-- Bo       80        3        3       2

How it works

All three number rows within an ordering. ROW_NUMBER always gives unique numbers (ties are broken arbitrarily unless you add a tiebreaker), RANK gives ties the same number and then skips, and DENSE_RANK gives ties the same number without skipping.

This snippet has been reviewed but was not executed by an automated test. Test it in your own environment before relying on it.

Related snippets