I have two tables, the first is players
--------------------------------------------- | player_id | player_score | player_game_id | --------------------------------------------- | 1 | 274 | 1 | --------------------------------------------- | 2 | 281 | 1 | --------------------------------------------- | 3 | 156 | 2 | --------------------------------------------- | 4 | 199 | 2 | --------------------------------------------- | 5 | 120 | 2 | ---------------------------------------------
And another table for games
----------------------- | game_id | game_name | ----------------------- | 1 | gameone | ----------------------- | 2 | gametwo | -----------------------
I would like to have this result
---------------------------------------------------- | player_id | player_score | player_rank | game_id | ---------------------------------------------------- | 2 | 281 | 1 | 1 | ---------------------------------------------------- | 1 | 274 | 2 | 1 | ---------------------------------------------------- | 4 | 199 | 1 | 2 | ---------------------------------------------------- | 3 | 156 | 2 | 2 | ---------------------------------------------------- | 5 | 120 | 3 | 2 | ----------------------------------------------------
Have anyone idea how to achieve this in mysql? Thank you very much
Advertisement
Answer
You want rank() or row_number():
select p.*,
rank() over (partition by player_game_id order by player_score desc) as rank
from players
order by player_game_id, player_score desc;
If two players have the same score, then rank() gives them the same ranking. row_number() will arbitrarily assign them adjacent rankings.
The above works on MySQL 8+. In earlier versions you have two options — subqueries and variables. Here is an example using a correlated subquery:
select p.*,
(select 1 + count(*)
from players p2
where p2.player_game_id = p.player_game_id and
p2.player_score > p.player_score
) as ranking
from players
order by player_game_id, player_score desc;
This is not as efficient as rank(). But with an index on players(player_game_id, player_score) and not too many players per game, then this should have reasonable performance.