MySQL update statement to store ranking positions

Question

I'm trying to get my head around a query and I just can't figure it out. I would appreciate if someone give me a pointer. As a simple example of what I'm trying to achieve, I have these records in the database

Score|Ranking
-------------
100  |0
200  |0
300  |0

And I would like the Ranking field to contain 1,2,3 based on who's got the highest score so the result should be:

Score|Ranking
-------------
100  |3
200  |2
300  |1

At the moment, I'm doing a for next loop for all these records but given that in reality that could be a few thousand - that could take forever! Does anyone have an idea on a magic query which would do this in one go?

Answer 1

Here's a way to do it:

SET @r=0;
UPDATE table SET Ranking= @r:= (@r+1) ORDER BY Score DESC;

/* use this if you just want to pull it from the db, but don't update anything */
SET @r=0;
SELECT *, @r:= (@r+1) as Ranking FROM table ORDER BY Score DESC;

Answer 2

In MySQL, you can use row_number.

Here's an example of using it in a SELECT:

select @rownum:=@rownum+1 ‘rank’, p.* 
from player p, (SELECT @rownum:=0) r 
order by score desc;

If you INSERT INTO using a SELECT like this, you will get your rankings.