MySQL update statement to store ranking positions

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

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.

Advertisement

Answer