178. Rank Scores

Description

SQL Schema

Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no "holes" between ranks. 

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

For example, given the above Scores table, your query should generate the following report (order by highest score): 

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

My Solution

Source Code

1
2
3
4
5
6
7
8

SELECT s1.Score, 
    (
        SELECT COUNT ( DISTINCT Score )
        FROM Scores AS s2
        WHERE s2.Score >= s1.Score
     ) as Rank
FROM Scores as s1
ORDER BY s1.Score DESC

Analysis

This one was very easy to implement but kind of hard to come up with the solution for It took me awhile to realize that if we just look at the unique salaries in the table, you can very easily figure out the rank. For any score, its rank is just the number of scores larger than it. So if the score is rank 1, then it's the largest. By only considering the unique scores in the table, we never have to deal with 'gaps' in rank when there are duplicates. This kind of tripped me up at first. The ranks of my scores would look something like 1, 2, 2, 4, ... Simply looking only at distinct scores instead completely fixed everything.