Reputation: 57
having a mysql table with multiple records belonging many different users like this:
1 , 50
1 , 75
1 , 40
1, 20
2 , 85
2 , 60
2 , 20
i need to get the rank of each id but after finding the sum of their score;
the rank should be the same if the total score for each player is the same.
this gives me the total for each player:
select id,sum(score) as total from table_scores group by id order by total desc;
is it posssible to find the sum like above and use it to rank the players in one query?
Upvotes: 1
Views: 3326
Reputation: 1365
Something big missing from the accepted answer. The rank needs to be bumped after a tie. If you've got 2 tied for 3rd place, there is no 4th place.
The following query is an adjustment of the accepted SQL to account for this and reset the rank variable (@r in the query) to match the row value. You can avoid the extra addition in the CASE/WHEN but initializing @row to 1 instead of 0 but then the row value is off by 1 and my OCD won't let that stand even if row number is not valuable.
select
id, total,
CASE WHEN @l=total THEN @r ELSE @r:=@row + 1 END as rank,
@l:=total,
@row:=@row + 1
FROM (
select
id, sum(score) as total
from
table_scores
group by
id
order by
total desc
) totals, (SELECT @r:=0, @row:=0, @l:=NULL) rank;
Upvotes: 2
Reputation: 1027
i find one more way to this problem... This one is based on JOIN clause
SET @rank = 0;
SELECT t1.id, t1.score, t2.rank
FROM (SELECT id, SUM(score) as score
FROM table_scores GROUP BY id ORDER BY score Desc) AS t1
INNER JOIN
(SELECT x.score, @rank:=@rank + 1 as rank FROM
(SELECT DISTINCT(SUM(score)) AS score
FROM table_scores
GROUP BY id ORDER BY score DESC) AS x) AS t2
ON t1.score = t2.score
Here is SQL Fiddle: http://sqlfiddle.com/#!9/2dcfc/16
P.S. it's interesting to see there is more then one way to solve a problem...
Upvotes: -1
Reputation: 49089
You can rank rows using variables:
select
id, total,
CASE WHEN @l=total THEN @r ELSE @r:=@r+1 END as rank,
@l:=total
FROM (
select
id, sum(score) as total
from
table_scores
group by
id
order by
total desc
) totals, (SELECT @r:=0, @l:=NULL) rank;
Please see it working here.
Upvotes: 1