SQL RANK

Ties share the same rank — and the rank after a tie jumps forward to account for it.

What & Why

RANK() gives tied rows the identical rank. The next row after a tie doesn't get the next consecutive number — it skips ahead by however many rows tied, so the ranks still add up to the row count.

See How It Works

BUSINESS QUESTION

Product wants feedback ratings ranked while preserving tied positions.

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 RANK preserve a tie and leave a gapStep 1 of 4

RANK() OVER (ORDER BY rating DESC)

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

Rating 5 receives rank one.

EXAMPLE QUERY

SELECT
  id AS feedback_id,
  rating,
  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.

See what's included →

Advanced business practice