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?

Answers


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.


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;

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.


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

i'm showing you my way of doing it [for interval sql update functions]

select:

set @currentRank = 0,
    @lastRating = null,
    @rowNumber = 1;
select
    *,
    @currentRank := if(@lastRating = `score`, @currentRank, @rowNumber) `rank`,
    @rowNumber := @rowNumber + if(@lastRating = `score`, 0, 1) `rowNumber`,
    @lastRating := `score`
from `table`
order by `score` desc

update:

set @currentRank = 0,
    @lastRating = null,
    @rowNumber = 1;
update 
    `table` r
    inner join (
        select
            `primaryID`,
            @currentRank := if(@lastRating = `score`, @currentRank, @rowNumber) `rank`,
            @rowNumber := @rowNumber + if(@lastRating = `score`, 0, 1) `rowNumber`,
            @lastRating := `score`
        from `table`
        order by `score` desc
    ) var on
        var.`primaryID` = r.`primaryID`
set
    r.`rank` = var.`rank`

i did not make any performance checks on this one except for testing that it works


Need Your Help

Spinning wheel on Android

android

I am trying to design a widget which looks like the "spinning wheel" (I am not sure it is appropriate name for it) widget that you can use to set up alarms or counters on the clock app provided wit...