MySQL + SQL · Lesson 80
ROW_NUMBER, RANK and DENSE_RANK
Ranking Functions
| Function | Behaviour on Ties |
|---|---|
| ROW_NUMBER() | unique 1,2,3 even for ties |
| RANK() | ties share rank, then skips (1,1,3) |
| DENSE_RANK() | ties share rank, no skip (1,1,2) |
Example
SELECT name, marks,
ROW_NUMBER() OVER (ORDER BY marks DESC) AS rn,
RANK() OVER (ORDER BY marks DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY marks DESC) AS drnk
FROM students;Summary
- ROW_NUMBER = always unique; RANK = skips after ties; DENSE_RANK = no skip.
- Great for "top N" and leaderboard queries.