How Ranking Functions Treat Ties
Concepts covered: Ranking, Row Numbering
The 3 ranking functions take the same OVER clause and agree on every row until 2 rows share a value in the ORDER BY. ROW_NUMBER gives tied rows consecutive numbers in an order the engine chooses. RANK gives them the same number and skips the numbers they consumed. DENSE_RANK gives them the same number and skips nothing. The interviewer is scoring whether you pick from the consumer's sentence rather than from habit, because all 3 produce plausible output on the same data and only 1 matches the request.
Everything else is shared: PARTITION BY, ORDER BY, the frame, the position in evaluation order. A candidate who describes them as 3 different tools has missed that. They are 1 tool with 3 settings for the moment 2 rows compare equal, and that setting is the whole decision.
| employee_id | salary | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|---|
| 101 | 180000 | 1 | 1 | 1 |
| 102 | 150000 | 2 | 2 | 2 |
| 103 | 150000 | 3 | 2 | 2 |
| 104 | 150000 | 4 | 2 | 2 |
| 105 | 120000 | 5 | 5 | 3 |
The table breaks ROW_NUMBER's tie by employee_id so it has fixed values. Without that, which tied row from 102 through 104 receives 2 is the engine's call, which section 3 takes up. RANK counts the rows ahead of a row: 105 has 4 rows above it and gets 5. DENSE_RANK counts the distinct values ahead: 105 is behind 2 salary levels and gets 3.
SELECT employee_id, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
RANK() OVER (ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
FROM employees| employee_id | salary |
|---|---|
| 101 | 180000 |
| 103 | 150000 |
| 102 | 150000 |
| 104 | 150000 |
| 105 | 120000 |
The animation runs all 3 over the same 5 rows with the same ORDER BY and nothing else. Watch the 150000 rows: RANK stamps 2 on all 3 and DENSE_RANK does the same, while ROW_NUMBER hands them 2 through 4 in whatever order the engine holds them. Then watch 105: RANK jumps to 5, DENSE_RANK steps to 3, ROW_NUMBER reaches 5. Everything that differs between the 3 columns happens at the tie and in the row after it.
What rn <= 3 returns under each
| Function | Rows returned | Who |
|---|---|---|
| ROW_NUMBER | 3 | 101 plus 2 of the 3 tied at 150000, engine's choice |
| RANK | 4 | 101, 102, 103, 104 |
| DENSE_RANK | 5 | every row: 120000 is the third distinct level |
ROW_NUMBER returns exactly N and decides the tie for you. RANK returns at least N: everyone tied at the boundary comes through, so 'top 3' delivers 4. DENSE_RANK returns the rows at the top N distinct values, which here is the whole department, and in a department where 40 people share 3 pay bands it returns 40. Each is the correct answer to a different sentence, and the interviewer's sentence is the spec.
The words that pick the function
| Phrase in the request | Function | What comes back |
|---|---|---|
| 'exactly 3, we have 3 bonus slots' | ROW_NUMBER with a tiebreak | 3 rows, no more |
| 'top 3, and if people tie include them all' | RANK | 3 or more rows |
| 'everyone at the top 3 salary levels' | DENSE_RANK | all rows at 3 distinct values |
| 'the 3 most recent orders per customer' | ROW_NUMBER | 3 rows; add order_id as the tiebreak |
The clarifying question, asked before SELECT, is 'If 2 people tie at position 3, do both appear? And is the report about 3 people or 3 pay levels?' The answer maps straight to a row of that table. Asked after the query is written, the same words mean 'fix the bug'. Asked before, they mean 'define the contract', and the rest of the conversation is about requirements, which is the conversation the interviewer wants to have.
Ties happen in every ranking column
'The 3 most recent orders per customer' ties whenever a batch import stamps 200 orders with the same created_at, and a top 3 by order count per store ties constantly because counts are small integers. The habit worth building is to ask 'can 2 rows share this value?' for every ORDER BY column. The answer is yes for any count or rating out of 5, and for dates without a time and rounded amounts. Saying 'ratings tie heavily, so this is a RANK question unless the carousel has fixed slots' is the kind of sentence that gets written in the interviewer's notes.
Probes after the query lands
Expect 1 of 3 probes after the query lands. 'What if 2 employees tie for third?' Answer with the consumer: RANK if both should appear, ROW_NUMBER with a stated tiebreak if the report needs 3. 'What if I want the top 3 salary levels?' DENSE_RANK, and say out loud that the row count is now unbounded. 'Why did you pick ROW_NUMBER?' is the probe that catches habit; the strong answer names the words in the request that forced it, such as 'exactly 3 bonus slots'. An answer that cites what the function does rather than what the consumer asked is the one that loses the point.
About this lesson
This section is part of the Top N Per Group: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.