Mysql

178. Score ranking

Medium difficulty

SQL schema

Write an SQL query to achieve score ranking.

If the two scores are the same, the two scores Rank the same. Please note that the next ranking after bisection should be the next consecutive integer value. In other words, there should be no "interval" between ranking.

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

For example, according to the Scores table given above, your query should return (sorted from high to low):

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

**Important: * * for MySQL solutions, if you want to escape reserved words used as column names, you can use apostrophes before and after keywords. For example, Rank

Solution 1: dedicated window function deny_ rank

select `Score`,
dense_rank() over (Order by Score desc) as `Rank` 
from Scores;

Operation results:

dense_ The row with parallel rank in the rank function will not occupy the position of the next name, that is, 1, 1, 2, 3, 3 and 4. This ranking.

Extension:

mysql four sorting functions:

Example:

select `Score`,
dense_rank() over (Order by Score desc) as `Rank` ,
rank() over (Order by Score desc) as `Rank1`,
row_number() over (Order by Score desc) as `Rank2`
from Scores;

Operation results:

According to the operation results:

1. rank function: results 1, 1, 3, 4, 4 and 6, that is, if there is a row with parallel ranking, it will occupy the position of the next ranking.

2,dense_rank function: results: 1, 1, 2, 3, 3, 4, that is, if there are rows with parallel ranking, they do not occupy the position of the next ranking.

3,row_number function: the results are 1, 2, 3, 4, 5 and 6, that is, the parallel ranking is not considered.

The fourth sorting function is:

Example:

select `Score`,
ntile(1) over (Order by Score desc) as `Rank1`,
ntile(2) over (Order by Score desc) as `Rank2`,
ntile(3) over (Order by Score desc) as `Rank3`,
ntile(4) over (Order by Score desc) as `Rank4`,
ntile(5) over (Order by Score desc) as `Rank5`,
ntile(6) over (Order by Score desc) as `Rank6`,
ntile(7) over (Order by Score desc) as `Rank7`
from Scores;

result:

ntile(1): result 1, 1, 1, 1, 1, 1

ntile(2): result 1, 1, 1, 2, 2, 2

ntile(3): result 1, 1, 2, 2, 3, 3

ntile(4): result 1, 1, 2, 2, 3, 4

ntile(5): result 1, 1, 2, 3, 4, 5

ntile(6): results 1, 2, 3, 4, 5, 6

ntile(7): results 1, 2, 3, 4, 5, 6

From the running results, nitile is sorted by groups. When the parameter is, nitile is sorted by groups.

Problem solution 2:

Example

select a.Score as Score,
(select count(distinct b.Score) from Scores as b where b.Score>=a.score)as `Rank`
from Scores as a
order by a.Score desc;

Interpretation:

In the sub query, the total number of scores greater than or equal to the current score will be retrieved, which is the ranking of the score. Take this question as an example:

Score: 4.00 - rank: 1 there are two that are greater than or equal to 4, which is 1 after de duplication.

Score: 4.00 - rank: 1 there are two that are greater than or equal to 4, which is 1 after de duplication.

Score: 3.85 - rank: 2 there are 3 4, 4 and 3.85 greater than or equal to 3.85. After weight removal, it is 2.

Score: 3.65 - rank: 3 there are 5 4, 4, 3.85, 3.65 and 3.65 greater than or equal to 3.65. After weight removal, it is 3.

Score: 3.65 - rank: 3 there are 5 4, 4, 3.85, 3.65 and 3.65 greater than or equal to 3.65. After weight removal, it is 3.

Score: 3.50 - rank: 4 there are 5 4, 4, 3.85, 3.65, 3.65 and 3.50 greater than or equal to 3.65. After weight removal, it is 4.