RANK & DENSE_RANK
RANK() and DENSE_RANK() are window functions, closely related to ROW_NUMBER(), that also number rows within a partition according to an ORDER BY. The difference is how they treat ties: rows with equal values in the ORDER BY expression receive the same rank instead of being arbitrarily split apart.
The two functions differ from each other only in what happens to the numbering immediately after a tie:
RANK() gives tied rows the same number, then skips ahead by the number of tied rows — so after two rows tied for rank 1, the next rank is 3, not 2.
DENSE_RANK() gives tied rows the same number too, but never skips — the next distinct value always gets the very next integer.
Side-by-side comparison
Consider a leaderboard of quiz scores:
SELECT player_name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num, RANK() OVER (ORDER BY score DESC) AS rnk, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk FROM quiz_scores;
player_name | score | row_num | rnk | dense_rnk ------------+-------+---------+-----+----------- Alice | 95 | 1 | 1 | 1 Bob | 95 | 2 | 1 | 1 Carla | 90 | 3 | 3 | 2 Dinesh | 85 | 4 | 4 | 3 Elena | 85 | 5 | 4 | 3 Farid | 80 | 6 | 6 | 4
Alice and Bob are tied at the top with 95 points. ROW_NUMBER() arbitrarily hands out 1 and 2. RANK() gives both of them 1, then jumps straight to 3 for Carla because two rows already occupied ranks 1 and 2. DENSE_RANK() also gives both of them 1, but Carla gets 2 — the next rank isn't skipped just because there was a tie.
Function | Ties get same value? | Skips numbers after a tie? | Typical use |
|---|---|---|---|
ROW_NUMBER() | No — always unique | N/A | Deduplication, pagination, top-N per group |
RANK() | Yes | Yes (leaves gaps) | Competition-style rankings (1st, 2nd, 2nd, 4th) |
DENSE_RANK() | Yes | No (no gaps) | Grouping into consecutive tiers |
Leaderboard use case
RANK() is the natural fit for anything that mirrors a real-world competition, where skipping placements after a tie is the expected convention — think Olympic medal standings, where two athletes tied for silver means the next athlete is in 4th place, not 3rd.
SELECT player_name, score, RANK() OVER (ORDER BY score DESC) AS leaderboard_position FROM quiz_scores ORDER BY leaderboard_position;
DENSE_RANK() is more useful when you want to group rows into a fixed number of ranked tiers without leaving gaps — for example, labeling products as belonging to price tier 1, 2, 3, and so on, where you care about how many distinct tiers exist, not the count of items ahead of them.
SELECT product_name, price, DENSE_RANK() OVER (ORDER BY price DESC) AS price_tier FROM products;
Contrast with ROW_NUMBER
ROW_NUMBER() never produces ties — every row gets a distinct integer even when the underlying values are identical, and which tied row gets which number is arbitrary unless the ORDER BY is made fully deterministic. Use ROW_NUMBER() when you need a strict, one-row-per-number mapping, such as picking exactly one row per group. Use RANK() or DENSE_RANK() when the tie itself is meaningful information that should be preserved in the output.