Ranking window functions assign positions to rows according to an ordering. The three common choices—ROW_NUMBER, RANK and DENSE_RANK—handle ties differently. Choosing the right one depends on whether the question asks for exactly N rows, competition-style rank, or the top N distinct metric values.
Key Takeaways at a Glance:
- •ROW_NUMBER() assigns a unique, sequential integer (1, 2, 3...) to each row with strictly no ties and no gaps.
- •RANK() assigns identical ranks to tied rows, but skips subsequent rank numbers (1, 2, 2, 4) leaving gaps.
- •DENSE_RANK() assigns identical ranks to tied rows WITHOUT skipping subsequent numbers (1, 2, 2, 3) with no gaps.
- •Top-N per group interview standard: Compute ranking inside a CTE, then filter 'WHERE rank <= N' in the outer query.
In Simple Words (The Mental Model)
If 3 students tie for 2nd place in a race, how do you rank them? ROW_NUMBER arbitrarily picks one to be 2nd, one 3rd, and one 4th. RANK says they are all 2nd place, and the next person is 5th place (Olympic style). DENSE_RANK says they are all 2nd place, and the next person is 3rd place (no skipped numbers).
Real-World Analogy
Think of school grade rankings with scores: [100, 90, 90, 80]. ROW_NUMBER gives: [1, 2, 3, 4]. RANK gives: [1, 2, 2, 4] (gap at 3). DENSE_RANK gives: [1, 2, 2, 3] (continuous dense sequence).