178_Rank Scores

[medium]

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 aboveScorestable, 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    |
+-------+------+

Solution 1: use JOIN

• JOIN original table with itself on condition that scores in second table are larger than or equal to that in first table

• GROUP BY id and count how many distinct score are larger than or equal to it.

SELECT s1.Score, COUNT(DISTINCT s2.Score) AS Rank
FROM Scores AS s1 JOIN Scores s2 ON s1.Score <= s2.Score
GROUP BY s1.Id
ORDER BY 1 DESC;

Solution 2: use subquery

• ORDER BY Score in descending order

• COUNT how many distinct score is larger than or equal to a score. The result is rank.

SELECT 
    s.Score,
    (
    SELECT COUNT(DISTINCT S2.Score)
    FROM Scores AS s2
    WHERE s2.Score >= s.Score    
    ) AS Rank
FROM Scores AS s
ORDER BY s.Score DESC;