Rank MySQL for multiple users

Question

I would need an help for a task I should implement. I have a table, in which are included following fields:

Username
Score
Competition

Each username can be present in the table several times, thus he can have several scores. In any case, a username can be associated to only one country.

Here below an example of my table:

Username !! Country || Score
Bob       ! Italy   !   100
Bob       ! Italy   !   150
Christian ! Italy   !   500
Christian ! Italy   !   200
Fred      | Germany |  80
Carl      | Germany |  700
Charlie   | France  |   400
Charlie   | France  |  280
Jean      | France  |   90

What I would get is a MYSQL command to get the ranking of each user in his country, considering only his best score and discarding the others.

Therefore, my ideal output should look like:

Christian ranked #1 in Italy
Bob ranked #2 in Italy
Carl ranked #1 in Germany

Fred ranked #2 in Germany

Charlie ranked #1 in France
Jean ranked #2 in France

I can order the users and have their rank, but having all the entries. What I would like to have is the ranking with just the best scores.

Any help is much appreciated,

Thanks a lot

And what have you tried? Please see [How to ask](https://stackoverflow.com/help/how-to-ask). — ArSeN, Mar 14 '20 at 18:17
An example would be helpful, however, the max method/function might be useful, `SELECT * FROM t WHERE myValue IN (SELECT max(myValue) From t); ` Source: https://stackoverflow.com/a/12257378/4612518 Good luck! — bananaforscale, Mar 14 '20 at 18:29

score 0 · Answer 1 · answered Mar 14 '20 at 18:54

0

You can do this with aggregation and window function rank(), if you are running MySQL 8.0

select 
    username,
    country,
    max(score) top_score,
    rank() over(partition by country order by max(score) desc) country_rank
from mytable
group by username, country
order by country, country_rank

answered Mar 14 '20 at 18:54

GMB

216,147
25
84
135

Thanks a lot, unfortunately I have a lower version of MySQL, so I guess I cannot deal with commands like "partition" because I get an error message. Thanks anyway for giving this hint. – klaus67 Mar 15 '20 at 14:20

Jannes Botis · Answer 2 · 2020-03-23T08:14:45.593

One way that this could be done is:

SELECT o1.Username, o1.Country, o1.maxScore, 
    (SELECT COUNT(DISTINCT s2.Username) 
     FROM `scores` s2
     WHERE o1.Country = s2.Country AND s2.Score > o1.maxScore) + 1 AS ranking
FROM (
     SELECT Username, Country, MAX(Score) AS maxScore
     FROM `scores` s1
     GROUP BY Username) o1

This works by:

1st getting the max score for each Username seen at alias "o1" (MAX(Score) AS maxScore)
2nd using the maxScore result found above and a subQuery "s2" to find the rank for each Username and Country

Since MAX(Score) AS maxScore is an aggregated result, we cannot use the subQuery directly in 1 query, but first we have to aggregate the results and use the resulted max scores.

Rank MySQL for multiple users

2 Answers2