Rank Scores
Problem
Write a SQL query to rank the scores. If two scores tie, they have the same rank, and the next rank is exactly one larger (DENSE_RANK).
SQL: SELECT score, DENSE_RANK() OVER (ORDER BY score DESC) AS 'rank' FROM Scores;
scores = [3.5, 3.65, 4.0, 3.85, 4.0, 3.65][(4.0,1),(4.0,1),(3.85,2),(3.65,3),(3.65,3),(3.5,4)]SELECT score, DENSE_RANK() OVER (ORDER BY score DESC) AS 'rank'
FROM Scores;Explanation
The whole query is really one built-in tool doing the work: DENSE_RANK(). It hands out ranking numbers to rows, and the secret is in the word "dense" — equal values share a rank and the next rank is always just one larger, with no gaps.
The OVER (ORDER BY score DESC) part tells the ranking how to look at the rows: sort them so the highest score is first, then number them from the top. That is why the biggest score gets rank 1.
Because it is DENSE_RANK (not plain RANK), two tied scores both get the same number and the score right below them gets the very next number. Plain RANK would instead skip numbers after a tie, which is not what we want here.
Example: scores [4.0, 4.0, 3.85, 3.65, 3.65, 3.5]. The two 4.0's both get rank 1, 3.85 gets rank 2, the two 3.65's both get rank 3, and 3.5 gets rank 4 — a clean, gap-free ranking.