Ranking functions
ROW_NUMBER, RANK, DENSE_RANK, NTILE — the four ranking window functions, and the subtle tie behaviour that bites.
What you'll learn
- Exactly how ROW_NUMBER, RANK, and DENSE_RANK differ on ties
- PARTITION BY to rank within each group
- NTILE for bucketing rows into quartiles or quintiles
- Picking the right function for the job
Before you start
SQL has four ranking functions. They look interchangeable, and they are not — the differences appear only on ties, and getting them wrong makes a leaderboard claim twelve people share first place.
The cleanest way to see it is one table. Imagine four scores — 100, 90, 90, 80 — and watch what each function does with the tie:
| Function | Output | Behaviour on the tie |
|---|---|---|
ROW_NUMBER | 1, 2, 3, 4 | always unique; the tie is broken arbitrarily |
RANK | 1, 2, 2, 4 | ties share a rank, then the next rank skips |
DENSE_RANK | 1, 2, 2, 3 | ties share a rank, the next rank does not skip |
NTILE(2) | 1, 1, 2, 2 | splits the rows into N equal buckets |
On real rows
Here are four players, two of whom tie at 90:
| name | team | score |
|---|---|---|
| Ana | A | 100 |
| Ben | A | 90 |
| Cara | B | 90 |
| Dan | B | 80 |
SELECT name, score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
RANK() OVER (ORDER BY score DESC) AS rk,
DENSE_RANK() OVER (ORDER BY score DESC) AS drk
FROM players;| name | score | rn | rk | drk |
|---|---|---|---|---|
| Ana | 100 | 1 | 1 | 1 |
| Ben | 90 | 2 | 2 | 2 |
| Cara | 90 | 3 | 2 | 2 |
| Dan | 80 | 4 | 4 | 3 |
Read the tied pair: ROW_NUMBER arbitrarily makes one of them 2 and the other 3; RANK calls both 2 and then jumps Dan to 4; DENSE_RANK calls both 2 and keeps Dan at 3. Same data, three different answers — choose deliberately.
PARTITION BY — rank inside each group
The usual question is not “rank everything” but “rank within each group.” PARTITION BY restarts the numbering whenever the group changes, which is how you get top-N per group:
WITH ranked AS (
SELECT name, team, score,
ROW_NUMBER() OVER (PARTITION BY team ORDER BY score DESC) AS rn
FROM players
)
SELECT team, name, score FROM ranked WHERE rn = 1;| team | name | score |
|---|---|---|
| A | Ana | 100 |
| B | Cara | 90 |
The numbering resets per team, so rn = 1 is each team’s leader. (Note the CTE: a window function can’t go directly in WHERE, so you compute it first and filter in the outer query.)
NTILE — split into equal buckets
NTILE(n) divides the ordered rows into n roughly equal buckets — the engine of decile reports and customer segmentation. NTILE(2) OVER (ORDER BY score DESC) over our four players puts Ana and Ben in bucket 1 (the top half) and Cara and Dan in bucket 2. When the rows don’t divide evenly, the extra rows go to the earlier buckets.
Practice
Quick check
Practice this in an interview
All questionsUse DENSE_RANK() to assign rank without gaps — the Nth distinct salary value is the row where DENSE_RANK equals N. RANK() would produce wrong results when ties occur, and a plain ORDER BY LIMIT/OFFSET approach ignores ties entirely.
NTILE(n) divides rows within a partition into n roughly equal buckets and assigns each row a bucket number from 1 to n. Rows are distributed as evenly as possible; when the row count is not evenly divisible, earlier buckets receive one extra row.
ROW_NUMBER assigns a unique sequential integer to every row regardless of ties. RANK assigns the same number to tied rows but skips subsequent positions. DENSE_RANK also assigns the same number to ties but never skips positions.
Rank salaries with DENSE_RANK() ordered descending and keep rank 2 — it handles duplicate salaries and generalises to the Nth-highest. A correlated subquery (the max salary strictly below the overall max) also works, while LIMIT 1 OFFSET 1 is only safe when no two people share a salary.