I have the following table:
userid compid round score bonus
2980 3 0 50 0
50 3 0 80 0
52 3 0 80 0
55 3 0 20 0
58 3 0 100 0
106 3 0 120 0
555 3 0 50 0
100 3 0 30 0
50 3 1 90 0
52 3 1 50 0
106 3 1 30 0
I want to be able to not only sum the score and bonus cols per user for all rounds, but rank them accordingly. It should be able to handle ties with the sequence 1,2,2,4,5,5,5,8:
Trick is I don't want to create a temp table or insert the ranking into the table. Also, i can not limit the return - so it need to rank the entire table regardless of rounds completed.
I have managed to rank the rows but can't seem to group them with the sum. And of course I have managed the sum separately - I just need to 'join' the two steps.
Obtaining sums per user:
SELECT userid,sum(score+bonus) as tscore from entries
group by userid order by tscore desc
userid tscore
50 170
106 150
52 130
58 100
2980 50
555 50
100 30
55 20
rank without the score sum : (thanks MySQL Cookbook)
SELECT @rownum := @rownum + 1 AS row,
@rank := IF(@prev_val!=score,@rownum,@rank) AS rank,
userid,
@prev_val := score AS score
FROM exodus_entries ORDER BY score DESC
row rank userid score
1 1 106 120
2 2 58 100
3 3 50 90
4 4 50 80
5 4 52 80
6 6 52 50
7 6 555 50
8 6 2980 50
9 9 100 30
10 9 106 30
11 11 55 20
Thanks for the time and help! All advice welcome!
SELECT @rownum := @rownum + 1 AS row,
@rank := IF(@prev_val!=score,@rownum,@rank) AS rank,
userid,
@prev_val := score AS score
FROM (
SELECT userid, sum(score+bonus) as score
from entries
group by userid
) exodus_entries
ORDER BY score DESC
I think this should do the trick:
SELECT userid,round, sum(score+bonus) as tscore from entries
group by userid, round order by tscore desc
Not tested though.
I had the same question and I executed SET @rownum = 0, @rank = 1, @prev_val = NULL;
before running the following codes:
SELECT @rownum := @rownum + 1 AS row,
@rank := IF(@prev_val!=total_number,@rownum,@rank) AS rank,
your_column,
@prev_val := total_number AS total_number
FROM your_table ORDER BY total_number DESC;