SQL ROW_NUMBER vs RANK

The only question that matters: what should happen to a tie?

What & Why

The ROW_NUMBER vs RANK pattern is used to compare unique row numbering with tie-aware ranking. This keeps the SQL aligned with one concrete business question and makes the result grain explicit before the query is reused.

See How It Works

BUSINESS QUESTION

Product wants to compare row numbers and tie-aware ranks 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,
  ROW_NUMBER() OVER (ORDER BY rating DESC, id) AS row_number,
  RANK() OVER (ORDER BY rating DESC) AS rating_rank
FROM product.feedback
ORDER BY row_number;

RESULT — unique row numbers and tie-aware ranks

feedback_id	rating	row_number	rating_rank
1	5	1	1
2	4	2	2
3	4	3	2
4	3	4	4

ROW_NUMBER gives the two rating-4 rows different positions, while RANK gives both rank 2 and leaves rank 3 unused.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice