Rank Scores Problem

Description

LeetCode Problem 178.

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

Important Note: For MySQL solutions, to escape reserved words used as column names, you can use an apostrophe before and after the keyword. For example Rank.

MySQL Solution

select score,
    (select count(distinct score) + 1 
     from scores ss 
     where ss.score > s.score) `rank`
from scores s
order by score desc

25 Dec 2020

Database

« Product Sales Analysis III Problem Team Scores In Football Tournament Problem »

LeetCode Database - Medium LeetCode 177 LeetCode 180 LeetCode 184 LeetCode 534 LeetCode 550 LeetCode 570 LeetCode 574 LeetCode 578 LeetCode 580 LeetCode 585 LeetCode 602 LeetCode 608 LeetCode 612 LeetCode 614 LeetCode 626 LeetCode 1045 LeetCode 1070 LeetCode 1077 LeetCode 1098 LeetCode 1107 LeetCode 1112 LeetCode 1126 More LeetCode Database MySQL Tutorials

Nth Highest Salary Problem

LeetCode 177. Write a SQL query to get the nth...

Database MySQL Leetcode

26 Dec 2020