RANK() vs DENSE_RANK()
Overview
If ROW_NUMBER() arbitrarily breaks ties, RANK() and DENSE_RANK() respect them. If Alice and Bob both score 100 on a test, they should both be ranked #1. The difference between the two is how they handle the next person (Charlie, who scored 90). RANK() will assign Charlie #3 (skipping #2 because 2 people tied for first). DENSE_RANK() will assign Charlie #2 (never leaving numerical gaps).
Syntax
-- Observe the mathematical difference!
SELECT
student_name,
score,
-- Strictly sequential: 1, 2, 3, 4
ROW_NUMBER() OVER (ORDER BY score DESC) as row_num,
-- Respects ties, skips numbers: 1, 1, 3, 4
RANK() OVER (ORDER BY score DESC) as standard_rank,
-- Respects ties, NO gaps: 1, 1, 2, 3
DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank
FROM test_scores;Common Pitfalls
- Using
RANK()for pagination. If you want to show 'Results 11-20', and you filter byRANK() BETWEEN 11 AND 20, you might get 0 results if the previous ranks tied massively and skipped the numbers 11 through 20! Always useROW_NUMBER()for strict pagination. - Forgetting the
ORDER BYinside theOVERclause. Ranking functions fundamentally cannot operate unless you explicitly tell them how to rank the data (e.g.,ORDER BY score DESC).
Interview Questions
RANK() vs DENSE_RANK()?Using RANK(), the 6th person receives Rank 6 (it leaves gaps for the 5 people ahead of them). Using DENSE_RANK(), the 6th person receives Rank 2 (it strictly never skips a numerical integer).
Real-World Example
Finding the 2nd Highest Salary in the company. (If the CEO and CTO both make 10M, they are both #1. The VP making 5M should be #2).
WITH RankedSalaries AS (
SELECT
emp_name,
salary,
-- DENSE_RANK guarantees the VP will be #2!
DENSE_RANK() OVER (ORDER BY salary DESC) as rank
FROM employees
)
SELECT * FROM RankedSalaries WHERE rank = 2;Check Your Knowledge
Test your understanding of RANK() vs DENSE_RANK() with these quick questions.