LeetCode: Rank Scores

Posted on February 12, 2018July 26, 2020 by braindenny

Rank Scores

Similar Problems:

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