Mysql Update Statement To Backfill Ranking By Each Id

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 |

Solution 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
orderby 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)
orderby id, score;