Part 93: Ranking Functions: ROW_NUMBER, RANK & DENSE_RANK
Ranking items based on scores, salaries, or timestamps is one of the most common SQL tasks.
1. The Differences Illustrated
Imagine three runners finish in: 10s, 10s (tie), and 12s.
| Value | ROW_NUMBER() |
RANK() |
DENSE_RANK() |
|---|---|---|---|
| 10s | 1 | 1 | 1 |
| 10s | 2 (Arbitrary tie-break) | 1 (Tie) | 1 (Tie) |
| 12s | 3 | 3 (Gaps after tie) | 2 (No gaps!) |
SELECT
first_name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
RANK() OVER (ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;
2. Top-N Per Category Pattern
Find the top 3 highest-earning employees in each department:
WITH RankedEmployees AS (
SELECT *, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
FROM employees
)
SELECT * FROM RankedEmployees WHERE rnk <= 3;
Key Takeaways
ROW_NUMBER()assigns sequential numbers with no ties.RANK()leaves gaps after ties.DENSE_RANK()assigns ties without skipping numbers.
In Part 94, we explore Tile Ranking with NTILE() for Percentiles and Buckets!