Rank in more tournaments and boards

Posted on

Question :

Source data is of sample

tournamet board player  score
    1      1    2       990
    1      1    4       980
    1      1    6       480
    1      1    7       450
    1      1    9       980
    1      1   10       510
    1      2    2       140
    1      2    4       110
    1      2    6       140
    1      2    7       110
    1      2    9        50
    1      2   10       120
    2      1    3       150
    2      1    5       150
    2      1    7       150
    2      1    8       130
    2      1   10       100
    2      1   11       400

How get the rank group by tournament and board with MYSQL

tournamet board player  score rank
    1      1    2       990   1
    1      1    4       980   2.5
    1      1    6       480   5
    1      1    7       450   6
    1      1    9       980   2.5
    1      1   10       510   4
    1      2    2       140   1.5
    1      2    4       110   4.5
    1      2    6       140   1.5
    1      2    7       110   4.5
    1      2    9        50   6
    1      2   10       120   3
    2      1    3       150   3
    2      1    5       150   3
    2      1    7       150   3
    2      1    8       130   5
    2      1   10       100   6
    2      1   11       400   1

Answer :

An ISO compliant query that will run on all major RDBMS without change – although the performance will not scale to large datasets.

    select a.tournamet, a.board, a.player, a.score,
           (
           (select count(*)
              from data b
             where b.tournamet=a.tournamet
               and b.board=a.board
               and b.score>a.score)
           + 1 + total -
           (select count(*)
              from data c
             where c.tournamet=a.tournamet
               and c.board=a.board
               and c.score<a.score)
           )/2 rank,
           total
      from data a
      join (select tournamet, board, count(score) total
              from data x
          group by tournamet, board) y
                on y.tournamet=a.tournamet
               and y.board=a.board
  order by tournamet, board, rank, player;


For MySQL specifically, subqueries are not allowed in the FROM clause, so the query is slowed down even more by putting the total count into yet another scalar, SELECT-level query:

select a.tournamet, a.board, a.player, a.score,
       (
       (select count(*)
          from data b
         where b.tournamet=a.tournamet
           and b.board=a.board
           and b.score>a.score)
       + 1 +
       (select count(*)
          from data c
         where c.tournamet=a.tournamet
           and c.board=a.board)
       -
       (select count(*)
          from data c
         where c.tournamet=a.tournamet
           and c.board=a.board
           and c.score<a.score)
       )/2 rank
  from data a

And below is a specific MySQL solution that only requires 2 table scans. Some have mentioned that multiple MySQL variables at the same level doesn’t always work, but it’s worked everywhere I’ve tried, so I would be interested to see where it breaks someone has an example.

select d.tournamet, d.board, d.player, d.score, z.rank
from
(
  select tournamet, board, score, avg(rank) rank
  from
  (
    select *,
           @r := if(@t=tournamet and @b=board, @r+1, 1) rank,
           @t := tournamet,
           @b := board
    from (select @t:=null, @b:=null, @r:=0) x, data
    order by tournamet, board, score desc
  ) y
  group by tournamet, board, score
) z
join data d
  on d.tournamet = z.tournamet
 and d.board = z.board
 and d.score = z.score
order by d.tournamet, d.board, d.score desc

The derived table in the middle is a classic ranking query, which
1. resets the rank for each group (tournamet/board); and
2. increases the rank for each record within the group
The ORDER BY in this query causes MySQL to process the records and output the ranking in the right sequence.

The achieve your averaged ranking, we need to take this query and further create averages. Finally, the aggregated data is JOINed back to the main table again to properly attribute the ranking to the original table rows. The final ORDER BY is added to display the data in a logical sequence.

Structure and some sample data

create table data (
  tournamet int, board int, player int, score int);
insert into data values (
    1      ,1,    2,       990),(
    1      ,1,    4,       980),(
    1      ,1,    6,       480),(
    1      ,1,    7,       450),(
    1      ,1,    9,       980),(
    1      ,1,   10,       510),(
    1      ,2,    2,       140),(
    1      ,2,    4,       110),(
    1      ,2,    6,       140),(
    1      ,2,    7,       110),(
    1      ,2,    9,        50),(
    1      ,2,   10,       120),(
    2      ,1,    3,       150),(
    2      ,1,    5,       150),(
    2      ,1,    7,       150),(
    2      ,1,    8,       130),(
    2      ,1,   10,       100),(
    2      ,1,   11,       400);

SQL Fiddle

Leave a Reply

Your email address will not be published.