1

I was trying to implement a query, that for each userid, rank the score and backfill the rank field, so that 

id | score | rank
1  |  100  | 0
1  |  200  | 0 
1  |  300  | 0
2  |  100  | 0
2  |  200  | 0
3  |  200  | 0

will become 

id | score | rank
1  |  100  | 3
1  |  200  | 2 
1  |  300  | 1
2  |  100  | 2
2  |  200  | 1
3  |  200  | 1

I saw a similar question here MySQL update statement to store ranking positions

However, in my case, how can I do the 'group by id' for each id?

Community
  • 1
  • 1
Freya Ren
  • 2,086
  • 6
  • 29
  • 39

1 Answers1

1

It might not be the prettiest way, but you can easily do something like:

set @rank = 0;
set @prev = 0;

select id, score, IF (id = @prev, @rank := @rank + 1, @rank := 1), @prev := id
from scores
order by id, score;

I guess you want the update statement as well, and that would be:

set @rank = 0;
set @prev = 0;

update scores
set rank = IF(id = @prev, @rank := @rank + 1, @rank := 1),
id = (@prev := id)
order by id, score;
rlanvin
  • 6,057
  • 2
  • 18
  • 24
  • I like your idea to use previous_id trick! But not sure what's the performance if the table has millions of records. Maybe if I create index on (id+score), the performance should be good? – Freya Ren Jul 27 '16 at 20:16
  • @FreyaRen "Good" is very relative. I have no idea in what context the query will be used, or the exact structure of the table, so I can't advise on that. All I can say is that updating "millions of records" is not going to be fast - no matter what you do. – rlanvin Jul 27 '16 at 20:23