SQL / WINDOW FUNCTIONS
Ranking rows with ROW_NUMBER, RANK, and DENSE_RANK
Choose between ROW_NUMBER, RANK, and DENSE_RANK by how each treats tied rows, and write window ORDER BY clauses that give repeatable numbering.
What you will learn
- Pick ROW_NUMBER for unique positions, RANK for gapped ties, DENSE_RANK for gapless ties
- Read RANK as 1 + rows ahead and DENSE_RANK as 1 + distinct sort values ahead
- Add a unique tiebreaker to ROW_NUMBER's ORDER BY so the numbering is repeatable
- Place NULLs deliberately with NULLS LAST, since ordering alone decides who is rank 1
Understanding Ranking rows with ROW_NUMBER, RANK, and DENSE_RANK
All three functions hand out integers according to the ORDER BY inside OVER, and on data with no duplicate sort keys they return exactly the same numbers. The differences live entirely in what happens to peers, meaning rows whose ORDER BY values are equal. ROW_NUMBER is a plain counter: it walks the ordered rows returning 1, 2, 3, and so on regardless of peers, so when two rows tie it still must give one of them the smaller number, and nothing in the SQL says which one. RANK and DENSE_RANK look at the peer group rather than the individual row, so every member of a tie receives the same number.
The two tie-respecting functions differ in what they count. RANK returns one plus the number of rows that sort strictly ahead of the current row, so three rows tied at the top all get 1 and the next row gets 4: the size of the gap is the width of the tie. DENSE_RANK returns one plus the number of distinct peer groups ahead, so it never skips a number, and MAX(DENSE_RANK) equals the number of distinct sort keys in the window. A handy invariant is that for every row DENSE_RANK <= RANK <= ROW_NUMBER, with all three equal when the ordering key is unique.
Because ties are defined by the ORDER BY list, that list is the real control knob. Appending a tiebreaker such as a primary key makes every row its own peer group, which is what ROW_NUMBER needs and what silently destroys a RANK-based leaderboard. NULLs are peers with each other, so whether they collect rank 1 or the last rank depends on NULLS FIRST or NULLS LAST and on your engine's default. Finally, the numbers come from the window's ORDER BY and not from the query's final ORDER BY, so without an outer ORDER BY you can get correct ranks attached to rows arriving in a shuffled order.
WITH scores(player, score) AS (
SELECT 'Ana', 95
UNION ALL SELECT 'Bo', 88
UNION ALL SELECT 'Cy', 88
UNION ALL SELECT 'Di', 84
UNION ALL SELECT 'Eve', 84
UNION ALL SELECT 'Fay', 70
)
SELECT player,
score,
ROW_NUMBER() OVER (ORDER BY score DESC, player) AS rn,
RANK() OVER (ORDER BY score DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense
FROM scores
ORDER BY score DESC, player;Ranking is a function of the peer groups formed by the window's ORDER BY: ROW_NUMBER ignores peers, RANK counts the rows ahead of the peer group, and DENSE_RANK counts the peer groups ahead.
Worked examples
NULLs form their own peer group
Shows that missing values tie with each other and that their rank depends entirely on NULLS FIRST or NULLS LAST.
WITH entries(runner, finish_min) AS (
SELECT 'Ana', 31
UNION ALL SELECT 'Bo', 34
UNION ALL SELECT 'Cy', NULL
UNION ALL SELECT 'Di', 34
UNION ALL SELECT 'Eve', NULL
)
SELECT runner,
finish_min,
RANK() OVER (ORDER BY finish_min) AS default_rank,
RANK() OVER (ORDER BY finish_min NULLS FIRST) AS nulls_first_rank
FROM entries
ORDER BY finish_min NULLS FIRST, runner;Example explained
Line 1Cy and Eve share a rank even though NULL = NULL is never true: peer grouping uses sort equivalence, not the equality operator.
Line 2PostgreSQL sorts NULLs last for ascending order, so default_rank puts the two missing times at 4, behind all three real times.
Line 3nulls_first_rank moves the same rows to 1 without changing any data, because 'ahead' is defined purely by the window ORDER BY.
Line 4Bo and Di both get 2 and no row gets 3, since RANK reserves a position for every tied row.
Reading the gap: what each function counts
Subtracting one from each rank exposes the quantity the function is really computing.
WITH readings(city, temp_c) AS (
SELECT 'Cairo', 38
UNION ALL SELECT 'Lima', 22
UNION ALL SELECT 'Oslo', 12
UNION ALL SELECT 'Perth', 38
UNION ALL SELECT 'Quito', 22
UNION ALL SELECT 'Riga', 12
)
SELECT city,
temp_c,
RANK() OVER (ORDER BY temp_c DESC) - 1 AS cities_hotter,
DENSE_RANK() OVER (ORDER BY temp_c DESC) - 1 AS hotter_temps
FROM readings
ORDER BY temp_c DESC, city;Example explained
Line 1RANK() - 1 is literally the number of rows sorting strictly ahead, which is why the jump after a two-row tie is 2.
Line 2DENSE_RANK() - 1 counts distinct temperatures ahead, so it advances by 1 per peer group and never skips.
Line 3Lima and Quito are peers only because the window ORDER BY names temp_c alone; adding city would split them and both columns would count them separately.
Line 4MAX over hotter_temps is 2, one less than the three distinct temperature values in the window.
Important notes
A frame clause is pointless on these three functions because their values come from peer groups, not from a frame; SQL Server rejects ROWS or RANGE on them outright.
Omitting ORDER BY inside OVER makes every row a single peer group, so RANK and DENSE_RANK return 1 for all rows and ROW_NUMBER hands out arbitrary numbers.
Common mistakes
Using ROW_NUMBER for a leaderboard where equal scores should share a place: two players on 88 become places 2 and 3, and which one gets 2 is not fixed by the query when the ORDER BY is not unique.
Treating DENSE_RANK as a row counter: on the six-row example its maximum is 4 because it counts distinct scores, so any arithmetic that assumes it counts rows is wrong wherever there are ties.
Leaving DESC out of the window ORDER BY: the query runs without error and hands rank 1 to the worst score, which usually goes unnoticed until someone reads the report.
Try it yourself
Change, predict, then run
Add a seventh player scoring 88 to the main query's CTE and write down all three columns for every row before running it, then execute it and check where RANK now jumps and why DENSE_RANK does not move.
Open the SQL workspaceCheck your understanding
A pay report must number each employee's position so that the three highest distinct salaries always produce exactly the position numbers 1, 2, and 3, no matter how many people share a salary. Which function does that?
- DENSE_RANK, because it numbers distinct salary values, so the third distinct salary is always 3
- RANK, because tied rows share a number and the gaps keep the position count accurate
- ROW_NUMBER, because it always produces consecutive numbers with no gaps
- RANK with the employee id added to the ORDER BY, because removing ties makes the numbers consecutive
Show answer
DENSE_RANK advances one number per distinct salary, so the third distinct salary is 3 regardless of tie sizes. RANK also shares numbers across ties but reserves a slot per tied row, so with salaries 100, 100, 90, 80 the third distinct salary lands at 4, not 3; ROW_NUMBER's consecutive numbers count people rather than salaries, giving the two employees on 100 the numbers 1 and 2.