MySQL row_number in group Man-Of-The-Match medals

Question

Our football team has each game 'Man of the match' voting results. Now I want the TOP 3 votings (or TOP 4..5 when equal votes) backgrounded in Gold, Silver, Bronze. I prefer using SQL query instead of php-code to define the medals.

My website query returns this now:

Game - Player - Points
Arsenal - Bart - 25
Arsenal - Joris - 17
Arsenal - Tim - 16
Arsenal - Tom - 16
Arsenal - Victor - 7
Arsenal - Jo - 1
Chelsea - Tim - 15
Chelsea - Bart - 15
Chelsea - Tom - 13
Chelsea - Ken - 10
Chelsea - Victor - 7
Chelsea  - Edris - 1

I am having trouble getting this result:

Game - Player - Points - Rank
Arsenal - Bart - 25 - 1
Arsenal - Joris - 17 - 2
Arsenal - Tim - 16 - 3 
Arsenal - Tom - 16 - 3
Arsenal - Victor - 7 - 4
Arsenal - Jo - 1 - 5
Chelsea - Tim - 15 - 1 
Chelsea - Bart - 15 - 1
Chelsea - Tom - 13 - 2
Chelsea - Ken - 10 - 3
Chelsea - Victor - 7 - 4
Chelsea  - Edris - 1 - 5

Even better would be this (would be perfect, but I guess way harder)

Game - Player - Points - Rank
Arsenal - Bart - 25 - 1
Arsenal - Joris - 17 - 2
Arsenal - Tim - 16 - 3 
Arsenal - Tom - 16 - 3
Arsenal - Victor - 7 - 5
Arsenal - Jo - 1 - 6
Chelsea - Tim - 15 - 1
Chelsea - Bart - 15 - 1
Chelsea - Tom - 13 - 3
Chelsea - Ken - 10 - 4
Chelsea - Victor - 7 - 5
Chelsea  - Edris - 1 - 6

Any suggestions please? It is just tbl_MOTM (Id, Game, Player, Points)

Answer 1

Maybe a little trick with user variables can be helpful.

First, you need to define the dataset you'll be sorting. Let's assume you already have a table like this:

Game | Player | Points

So your query should be something like:

select game, player, points
from your_table
-- Where conditions go here
order by points desc

So, now, let's use a variable to do what you need:

select a.game, a.player
     , @rank := @rank + (case when a.points != @points then 1 else 0 end) as rank
     , @points := a.points as points
from (select @rank := 0, @points := 0) as init
   , your_table as a
-- Where conditions go here
order by a.points desc

If you want to filter this to get all the players with rank 5 or above (and only five or less rows):

select b.*
from (
    select a.game, a.player
         , @rank := @rank + (case when a.points != @points then 1 else 0 end) as rank
         , @points := a.points as points
    from (select @rank := 0, @points := 0) as init
       , your_table as a
    -- Where conditions go here
    order by a.points desc
) as b
where rank <= 5
order by rank
limit 5

Of course, with a clever ussage of case...end you can define any conditions to reset the ranking. If you want to reset the @rank for each game (attention: you must put the columns in a specifi order for this to work; remember that sql statements using user-variables are evaluated left-to-right and top-to-bottom):

select a.player
     , @rank := (case 
                    when a.game != @game then 1
                    when a.points != @points then @rank
                    else @rank + 1
                end) as rank
     , @game := a.game as game
     , @points := a.points as points
from
    (select @game := ''
          , @points := 0
          , @rank := 0
    ) as init,
    your_table as a
-- Where conditions go here
order by a.game, a.points desc

If you need the columns in a specific order, enclose the above query in a subquery, or create a temporary table (I prefer this):

drop table if exists temp_result;
create temporary table temp_result
select a.player
     , @rank := (case 
                    when a.game != @game then 1
                    when a.points != @points then @rank
                    else @rank + 1
                end) as rank
     , @game := a.game as game
     , @points := a.points as points
from
    (select @game := ''
          , @points := 0
          , @rank := 0
    ) as init,
    your_table as a
-- Where conditions go here
order by a.game, a.points desc;
alter table temp_result
    add index idx_player(player),
    add index idx_game(game),
    add index idx_rank(rank);
-- Select top-5 players for each game:
select game, player, points, rank
from temp_result
where rank <= 5
order by game, rank;

Remember: temporary tables are visible only to the connection that has created it, and are deleted once the conection that created them is closed or killed.

Answer 2

@Barranka:

Thanks a lot, I could almost just copy-paste your code, minor edit needed to be made here:

select a.player
     , @rank := (case 
                    when a.game != @game then 1
                    when a.points = @points then @rank -- EDITED != to =
                    else @rank + 1
                end) as rank
     , @game := a.game as game
     , @points := a.points as points
from
    (select @game := ''
          , @points := 0
          , @rank := 0
    ) as init,
    your_table as a
-- Where conditions go here
order by a.game, a.points desc

This will give the result as expected in my first question, it will not jump the ranking from 1 to 4 when there 3 people having the nr1 score. But that is something I can do myself with a little extra effort. Thanks a lot!