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
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;