SQL RANK vs DENSE_RANK

Both treat ties as equal — they only disagree about what happens right after.

What & Why

RANK and DENSE_RANK agree completely on how to handle the tie itself. They disagree on the row immediately after it.

See How It Works

BUSINESS QUESTION

Product wants both tie-aware ranking conventions shown across feedback ratings 5, 4, 4, and 3.

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

EXAMPLE QUERY

SELECT
  id AS feedback_id,
  rating,
  RANK() OVER (ORDER BY rating DESC) AS rank_with_gaps,
  DENSE_RANK() OVER (ORDER BY rating DESC) AS dense_rank
FROM product.feedback
ORDER BY rank_with_gaps, feedback_id;

RESULT — rank gaps compared with dense ranks

feedback_id	rating	rank_with_gaps	dense_rank
1	5	1	1
2	4	2	2
3	4	2	2
4	3	4	3

The rating-4 tie makes RANK skip position 3, while DENSE_RANK assigns the next distinct rating position 3.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice