LeetCode: Rank Scores Posted on February 12, 2018July 26, 2020 by braindenny Rank Scores Similar Problems: CheatSheet: SQL & MySql CheatSheet: Leetcode For Code Interview CheatSheet: Common Code Problems & Follow-ups Tag: #sql 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 | +-------+------+ Github: code.dennyzhang.com Credits To: leetcode.com Leave me comments, if you have better ways to solve. Solution: Add rank and prev for group id ## https://code.dennyzhang.com/rank-scores select Score, @rank := @rank + (@prev <> (@prev := Score)) as Rank from Scores, (select @rank := 0, @prev := -1) as rows order by Score desc Solution: Add rank and prev for group id ## https://code.dennyzhang.com/rank-scores select Score, CAST(Rank as UNSIGNED) as Rank from (select Score, @rank := case when Score = @prev then @rank else @rank+1 end as Rank, @prev := Score as prev from Scores, (select @rank := 0, @prev := -1) as rows order by Score desc) as tt Solution 2 ## https://code.dennyzhang.com/rank-scores select ranking.Score as Score, ranking.Ranking as Rank from Scores inner join (select t1.Score as Score, count(1) as Ranking from (select distinct Score from Scores) as t1 inner join (select distinct Score from Scores) as t2 where t1.Score <= t2.Score group by t1.Score) as ranking on Scores.Score = ranking.Score order by ranking.Score desc Post Views: 4 Post navigation LeetCode: Tenth LineLeetCode: Swap Salary Leave a Reply Cancel replyYour email address will not be published.Comment Name Email Website