Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
@Mat Looks like "joint 2nd=2.5", "joint 1st=1.5" etc –  Phil Oct 28 '12 at 17:37
    
if tie places is 1 and 2, rank is average 1.5, board 1 tournament 2, 2-3-4 is average 3 –  otm Oct 28 '12 at 20:19
add comment

1 Answer 1

up vote 2 down vote accepted

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

share|improve this answer
    
Thanks for answer. With sample data it is works good, with my real source data is litle problem. If source is table, all is OK, if source is view, with the same structure and data, rank is bad. –  otm Oct 29 '12 at 9:02
    
    
Yes, with the same data. The first select work good on both source data, but no work as view. Error message is View's SELECT contains a subquery in the FROM clause –  otm Oct 29 '12 at 10:07
    
If you're starting from views, it would have helped to state that. If you're planning to create a view from the query, it would also help to state that in the question. –  孔夫子 Oct 29 '12 at 10:09
    
Answer updated. See 2nd query –  孔夫子 Oct 29 '12 at 10:16
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.