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?

score 27 · Answer 1 · answered Jul 07 '10 at 17:12

27

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;

answered Jul 07 '10 at 17:12

quantumSoup

27,197
9
43
57

1

See DOK's version - it doesn't require the SET statement, it's entirely self contained. – OMG Ponies Jul 07 '10 at 17:47
3

Self-contained in the sense that it's in a single query, yes; but that's because he's setting @rownum in the SELECT subquery. Which I think looks messier. – quantumSoup Jul 07 '10 at 17:55
1

Beauty is in the eye of the beholder, but the ability to submit a single statement is less likely to have problems – OMG Ponies Jul 07 '10 at 23:17
I think this should be sort by ascending? – Funzo Sep 17 '19 at 12:24

score 6 · Accepted Answer · answered Jul 07 '10 at 16:53

6

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.

answered Jul 07 '10 at 16:53

DOK

32,337
7
60
92

+1: Normally I'd say this should be in a view, but MySQL views don't allow variable use. There's an alternative using COUNT in a subselect to get the ranking value that would work in a view I imagine... – OMG Ponies Jul 07 '10 at 17:10

score 5 · Answer 3 · answered Jul 07 '10 at 17:03

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.

score 3 · Answer 4 · answered Dec 15 '20 at 12:33

If you are using MySQL 8 so can you use the new function RANK()

SELECT
    score,
    RANK() OVER (
       ORDER BY score DESC
    ) ranking
FROM
    table;

Depending on how you want to display the ranking with even score so can you also check out DENSE_RANK()

And as an UPDATE:

WITH
   ranking AS(
   SELECT
      score,
      RANK() OVER (
         ORDER BY score DESC
      ) ranking
    FROM
        table
)
UPDATE
    table,
    ranking r
SET
    table.ranking = r.ranking
WHERE
    table.score = r.score

score 2 · Answer 5 · edited Sep 23 '15 at 03:08

2

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;

edited Sep 23 '15 at 03:08

Raptor

53,206
45
230
366

answered Jan 04 '13 at 07:28

Colen

21
1

score 1 · Answer 6 · answered Nov 07 '12 at 13:17

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

MySQL update statement to store ranking positions

6 Answers6

Linked