RANK vs DENSE_RANK vs ROW_NUMBER in MySQL

These three ranking functions look interchangeable until your data has ties. Then each behaves differently — and choosing the wrong one changes your top-10 list. A favourite interview question for data roles.

Key Points

• ROW_NUMBER: unique sequence 1,2,3,4 — ties broken arbitrarily
• RANK: ties share a rank, next rank skips (1,2,2,4)
• DENSE_RANK: ties share a rank, no gaps (1,2,2,3)
• Top-N per group: filter ROW_NUMBER() OVER (PARTITION BY …) <= N in an outer query
• Use RANK for competition-style leaderboards
• Use ROW_NUMBER for deduplication (keep row 1 of each group)