SQL DENSE_RANK
Ties still share a rank — but the rank after a tie never skips ahead.
What & Why
DENSE_RANK() handles ties exactly like RANK — but the next distinct value always gets the very next integer, with no gap. "Dense" means no rank number is ever skipped.
See How It Works
BUSINESS QUESTION
Product wants dense feedback-rating ranks without gaps after ties.
| id | user_id | feature_id | rating | nps_score | comment | created_at |
|---|---|---|---|---|---|---|
| 1 | 101 | 1 | 5 | 9 | Clear and useful | 2024-01-25 12:15:00+00 |
| 2 | 102 | 2 | 4 | NULL | Export worked well | 2024-02-20 15:40:00+00 |
| 3 | 103 | 1 | 4 | 7 | Helpful setup flow | 2024-03-18 09:05:00+00 |
| 4 | 104 | 3 | 3 | NULL | Needs clearer timing | 2024-04-08 17:30:00+00 |
Watch DENSE_RANK preserve a tie without a gapStep 1 of 4
DENSE_RANK() OVER (ORDER BY rating DESC)
| feedback_id | rating | rating_rank |
|---|---|---|
| 1 | 5 | 1 |
| 2 | 4 | 2 |
| 3 | 4 | 2 |
| 4 | 3 | 3 |
Rating 5 receives dense rank one.
EXAMPLE QUERY
SELECT
id AS feedback_id,
rating,
DENSE_RANK() OVER (ORDER BY rating DESC) AS rating_rank
FROM product.feedback
ORDER BY rating_rank, feedback_id;This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario