DENSE_RANK()
SQL · Window Functions
Theory
DENSE_RANK() works like RANK(): tied values share the same number. The difference is that it never leaves gaps. After two people tie at rank 2, the next person is rank 3, not 4. In other words, DENSE_RANK counts distinct values: rank n means 'the n-th highest distinct value'.
That makes it the natural answer to the classic interview question 'find the second (or Nth) highest salary'. Compute DENSE_RANK() OVER (ORDER BY salary DESC) and keep rows where it equals 2; ties at the top do not push the second salary out of reach. The three ranking functions only differ on ties. On 75000, 60000, 60000, 52000, 45000 they give: ROW_NUMBER 1,2,3,4,5; RANK 1,2,2,4,5; DENSE_RANK 1,2,2,3,4. Remembering that one line answers most interview questions about them.
Why we use it
Some questions are about distinct levels, not positions: the Nth highest salary, the top 3 price points, grade bands. DENSE_RANK gives consecutive numbers per distinct value so 'rank = N' always means the Nth distinct value.
When to use it
Use DENSE_RANK for 'Nth highest value' questions and when gaps would confuse users. Use RANK when the gap is meaningful (how many are ahead), and ROW_NUMBER when you need exactly one row per position.
How it works
- Partition is sorted by the window ORDER BY
- First row gets rank 1
- Same value as previous row? Yes: Same rank. No: Previous rank + 1 (no gap).
- Repeat for every row in the partition
- Consecutive ranks: 1, 2, 2, 3
Syntax
DENSE_RANK() OVER (ORDER BY col)Example
SELECT name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employees;Key points
- Ties share a rank; the next rank is consecutive (1, 2, 2, 3)
- DENSE_RANK = N picks the Nth highest distinct value
- Highest DENSE_RANK equals the number of distinct values
- Common interview question: RANK vs DENSE_RANK vs ROW_NUMBER
- Filter it in a subquery/CTE, not directly in WHERE