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)

That's not as easy in MySQL as in other dbms, because of the lack of analytic functions. Why do you need this anyway? Don't you use a programming language to show the results? Java, PHP, anything? Then use this to color the first ranks. No need for a rank number in my opinion. — Thorsten Kettner, Nov 10 '14 at 17:48
This is a "top n per group" problem, and it is **NOT** easy to do in mysql. — Marc B, Nov 10 '14 at 17:58
I am used to using Microsoft MSSQL and views/stored procedures. I need the rank so I can create a tbl_RankColorCode to define the background of the records in the php website. But in Mysql this seems quite hard, so I guess indeed I will need to write php loops to define the Rankcolors there. But I will first try the solution below. Thanks. — Joris, Nov 10 '14 at 18:18

Barranka · Accepted Answer · 2014-11-10T18:36:00.570

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.

Thank you I will test this. you say for example "select game, player, points from your_table order by points desc" but that should be "order by game, points desc". So that makes it harder? — Joris, Nov 10 '14 at 18:21
@Joris not necesarily... it's simply a matter of defining the "reset" condition inside the `case...end` with care — Barranka, Nov 10 '14 at 18:28

score 0 · Answer 2 · answered Nov 11 '14 at 12:39

@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!

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

2 Answers2