Databases & SQL Practice Question 9: Row Number vs Rank
Databases & SQL · Test 1 · question 9 of 10
The table scores(name, score) contains: ('Ana', 90), ('Ben', 85), ('Cai', 90), ('Dev', 80).
SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS rn, RANK() OVER (ORDER BY score DESC) AS rk FROM scores ORDER BY score DESC;
Reading the four result rows from top to bottom, what are the values of rn and rk?
Show answer and explanation
Answer:
Sorted by score descending, the rows are 90, 90, 85, 80.
ROW_NUMBER() numbers the rows of the partition 1, 2, 3, ... and never repeats a number, even for ties. So rn is 1, 2, 3, 4. Which of Ana and Cai gets 1 and which gets 2 is not defined, because they tie on the only sort key. Add a tiebreaker (for example ORDER BY score DESC, name) if you need that to be deterministic.
RANK() gives tied rows (peers) the same rank and then leaves a gap. Both 90s get 1, and the next row gets 3 because two rows come before it. So rk is 1, 1, 3, 4. Ben's rank is 3, not 2.
Option A treats RANK() like ROW_NUMBER() and ignores the tie.
Option B gives ROW_NUMBER() duplicates, which it never produces. It also gives rk the gap-free values 1, 1, 2, 3, which is what DENSE_RANK() would return, not RANK().
Option D has the correct rk but again gives ROW_NUMBER() duplicates.
Reference: PostgreSQL: Window Functions (row_number, rank, dense_rank)