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.
- order by Score desc.
- The Rank is consecutive.
Find all the Score in the Scores table.
SELECT DISTINCT Score FROM Scores; -- tmp
COUNT the Score in the Scores table.
SELECT count(*) FROM tmp WHERE tmp.Score >= s1.Score -- rank
Associate and get the result
SELECT Score, rank AS 'Rank' FROM Scores ORDER BY Score DESC; -- See the following
SELECT s1.Score, ( SELECT count(*) FROM ( SELECT DISTINCT s2.Score FROM Scores s2 ) tmp WHERE tmp.Score >= s1.Score ) AS 'Rank' FROM Scores s1 ORDER BY s1.Score DESC; -- 10 / 10 test cases passed. -- Runtime:956 ms