mysql get ranking position grouped with joined table

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

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.