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);