MySQL update statement to store ranking positions

Question

Hi, 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?

Thanks in advance!

Leon

Answer 1

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.

Answer 2

This creates an inline update statement that will rank your players incrementing by the variable @rc. I've used it many times in very similar cases, it works well and keeps it all on the DB side.

SET @rc = 0;
UPDATE players JOIN (SELECT @rc := @rc + 1 AS rank, id FROM players ORDER BY rank DESC)
AS order USING(id) SET players.rank = order.rank;

id is assumed to be the primary key for your players table.

Answer 3

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;