ROW_NUMBER vs RANK vs DENSE_RANK: The Difference That Breaks Your Leaderboard
The three ranking functions differ exactly when values tie — and that edge case is where leaderboards and top-N reports break. Side-by-side output table, plus when to use each one.
The Difference in One Sentence
On ties, ROW_NUMBER assigns arbitrary distinct numbers, RANK skips numbers after ties (1, 1, 3), and DENSE_RANK does not skip (1, 1, 2). Everything else — syntax, OVER clause, partitioning — is identical. The bug always shows up at the ties.
See It Side by Side
Given scores 100, 100, 90, 80:
SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS rn, RANK() OVER (ORDER BY score DESC) AS rnk, DENSE_RANK() OVER (ORDER BY score DESC) AS drnk FROM players;
| name | score | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|---|
| Alice | 100 | 1 | 1 | 1 |
| Bob | 100 | 2 | 1 | 1 |
| Carol | 90 | 3 | 3 | 2 |
| Dave | 80 | 4 | 4 | 3 |
Which One to Use
ROW_NUMBER — deduplication and picking one row per group. "Latest record per customer" is a ROW_NUMBER job: number by date DESC, filter rn = 1. You always get exactly one row per group, which is what dedup needs.
RANK — competition leaderboards where tied players share a position and the next player is pushed down (golf, Olympic scoring). "Top 3 finishers" includes ties correctly: WHERE rnk <= 3.
DENSE_RANK — "top N distinct values". Salaries: top 5 salary levels, all employees at those 5 levels. WHERE drnk <= 5 never cuts a tied group in half.
The Classic Bug: Top-N with Ties Cut in Half
"Get the top 3 scores" with ROW_NUMBER returns 3 rows — but if two players tie for 2nd, one of them gets rn=3 and the other rn=4 and disappears. With RANK, both keep rank 2 and the query returns 4 rows, all correct. Decide intentionally whether your report wants exactly N rows (ROW_NUMBER) or all rows at the top N positions (RANK).
Per-Group Ranking
All three functions partition: rank players within each game mode with OVER (PARTITION BY mode ORDER BY score DESC). The N in "top N per group" is where DENSE_RANK-vs-ROW_NUMBER thinking matters most — per-category bestsellers are usually a ROW_NUMBER problem, while "top 3 rating tiers" is DENSE_RANK.
Deterministic Ties
When rows tie on the sort key, ROW_NUMBER returns an arbitrary order that can differ between runs. Add a tiebreaker column: ORDER BY score DESC, updated_at ASC — quieter leaderboards, reproducible exports.
Try It Yourself
Use our free SQL generator — type "rank players by score, keeping ties at the same rank" and it writes the RANK version for your dialect.