nandini_rao0 views
I rank students by score with a window function and the numbers skip: 1, 2, 2, 4. I expected 1, 2, 2, 3.
SELECT name, score, RANK() OVER (ORDER BY score DESC) AS position
FROM scores;
Is something wrong with my query, or is that how it works?
Be kind and specific. Read the community guidelines.
Accepted answer
Not a bug, that is what RANK() is defined to do. Rows that tie share a rank, and the next rank skips as many numbers as there were tied rows. Two students tied for second place, so nobody is third.
There are three functions and each answers a different question:
SELECT name, score,
ROW_NUMBER() OVER (ORDER BY score DESC, name) AS row_number,
RANK() OVER (ORDER BY score DESC) AS rank,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM scores;
With scores 90, 85, 85, 80 that returns:
| name | score | row_number | rank | dense_rank |
|---|---|---|---|---|
| Ana | 90 | 1 | 1 | 1 |
| Ben | 85 | 2 | 2 | 2 |
| Cara | 85 | 3 | 2 | 2 |
| Dev | 80 | 4 | 4 | 3 |
You want DENSE_RANK(): ties share a rank and the next rank follows without a gap. Use ROW_NUMBER() only when every row must get a different number, and then add a tie-breaker to the ORDER BY so the result is the same every run.
That is exactly it, DENSE_RANK() gives 1, 2, 2, 3. Thank you for the table, it made the difference between the three obvious.