ROW_NUMBER vs RANK vs DENSE_RANK: What is the Difference?
Understand the exact differences between ROW_NUMBER, RANK, and DENSE_RANK in SQL with a worked tie-breaking example.
Expected Interview Answer
ROW_NUMBER assigns a unique, strictly increasing integer to every row with no ties, RANK assigns the same rank to tied rows but then skips the following rank numbers, and DENSE_RANK also assigns the same rank to tied rows but never skips any subsequent rank number.
All three are ranking window functions used with an ORDER BY inside OVER(), typically within a PARTITION BY group. Given values 90, 90, 80: ROW_NUMBER produces 1, 2, 3; RANK produces 1, 1, 3 because it reserves rank 2 for the tie it skipped; DENSE_RANK produces 1, 1, 2 because it never leaves a gap. Choosing the right one matters for correctness — ROW_NUMBER is used when you need exactly one row per group, such as deduplication, RANK is used when you want ties to reflect how many rows outrank the next distinct value, and DENSE_RANK is used when you want a compact, gap-free ranking of distinct values.
- ROW_NUMBER guarantees exactly one unique number per row
- RANK reflects the true count of rows ahead of a tie
- DENSE_RANK keeps ranking values contiguous with no gaps
- All three compute directly without self-joins or subqueries
AI Mentor Explanation
Picture a tournament's top run-scorer table where two batters tie on exactly 450 runs. ROW_NUMBER would arbitrarily list one at position four and the other at five even though they are tied, useful only when you need one distinct slot per player. RANK lists both at position four and then jumps the next batter straight to position six, reflecting that two players sit ahead of them. DENSE_RANK lists both at four and gives the next batter position five, keeping the position numbers unbroken.
Step-by-Step Explanation
Step 1
Decide if ties should share a rank
Use ROW_NUMBER if every row needs a unique number regardless of ties; otherwise use RANK or DENSE_RANK.
Step 2
Decide if gaps after ties matter
Use RANK to preserve gaps that reflect the count of tied rows; use DENSE_RANK for a gap-free sequence.
Step 3
Write the OVER clause with ORDER BY
All three functions require ORDER BY inside OVER() to define the ranking sequence.
Step 4
Add PARTITION BY if needed
Include PARTITION BY to restart the ranking independently within each group, such as per department.
What Interviewer Expects
- Precise definition of how each function treats tied rows
- A worked example showing the numeric difference between all three
- Knowledge that ORDER BY inside OVER() is required for all three
- Ability to pick the right function for deduplication versus ranking display
Common Mistakes
- Saying RANK and DENSE_RANK behave identically
- Using ROW_NUMBER for deduplication without realizing ties get an arbitrary order
- Forgetting that all three require ORDER BY inside OVER()
- Confusing PARTITION BY with a filter rather than a grouping mechanism
Best Answer (HR Friendly)
“ROW_NUMBER gives every row a unique number even if values tie, RANK gives tied rows the same number but then skips ahead, and DENSE_RANK gives tied rows the same number without leaving any gap afterward. I would pick ROW_NUMBER when I need exactly one row per group, and RANK or DENSE_RANK when I want to show a meaningful ranking that respects ties.”
Code Example
SELECT
student_name,
score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
RANK() OVER (ORDER BY score DESC) AS rank_val,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_val
FROM Scores;
-- Scores 90, 90, 80 produce:
-- row_num: 1, 2, 3
-- rank_val: 1, 1, 3
-- dense_rank_val: 1, 1, 2Follow-up Questions
- How would you use ROW_NUMBER to remove duplicate rows?
- Why does RANK skip numbers after a tie but DENSE_RANK does not?
- How does NTILE differ from these three ranking functions?
- What happens if you omit ORDER BY inside the OVER clause for these functions?
MCQ Practice
1. Given tied scores 90, 90, 80 ordered descending, what does RANK() produce?
RANK assigns the same rank to ties and then skips the number of tied rows, so the next distinct value gets rank 3.
2. Which ranking function never leaves a gap in its output sequence after a tie?
DENSE_RANK assigns the same value to ties but continues the next rank immediately, with no skipped numbers.
3. Which function is most appropriate for selecting exactly one row per group during deduplication?
ROW_NUMBER assigns a strictly unique number to every row, so filtering row_num = 1 per partition yields exactly one row per group.
Flash Cards
What does ROW_NUMBER guarantee? — A unique, strictly increasing number for every row, even when values tie.
How does RANK treat ties? — Tied rows get the same rank, and the next rank number is skipped by the count of ties.
How does DENSE_RANK treat ties? — Tied rows get the same rank, and the next rank number continues with no gap.
When would you use ROW_NUMBER over RANK? — When you need exactly one unique row per group, such as for deduplication.