Update the rank in a MySQL Table

Asked 28/4, 2010 at 6:2 Answered 22/10, 2011 at 14:42

I have the following table structure for a table Player

Table Player {  
Long playerID;  
Long points;  
Long rank;  
}

Assuming that the playerID and the points have valid values, can I update the rank for all the players based on the number of points in a single query? If two people have the same number of points, they should tie for the rank.

UPDATE:

I'm using hibernate using the query suggested as a native query. Hibernate does not like using variables, especially the ':'. Does anyone know of any workarounds? Either by not using variables or working around hibernate's limitation in this case by using HQL?

Multiversity answered 28/4, 2010 at 6:2 Comment(1)

@sammichy: Regarding your edit on hibernate, you may want to post a new question, as it will get more attention. – Betweentimes 28/4, 2010 at 21:7

One option is to use a ranking variable, such as the following:

UPDATE   player
JOIN     (SELECT    p.playerID,
                    @curRank := @curRank + 1 AS rank
          FROM      player p
          JOIN      (SELECT @curRank := 0) r
          ORDER BY  p.points DESC
         ) ranks ON (ranks.playerID = player.playerID)
SET      player.rank = ranks.rank;

The JOIN (SELECT @curRank := 0) part allows the variable initialization without requiring a separate SET command.

Further reading on this topic:

Test Case:

CREATE TABLE player (
   playerID int,
   points int,
   rank int
);

INSERT INTO player VALUES (1, 150, NULL);
INSERT INTO player VALUES (2, 100, NULL);
INSERT INTO player VALUES (3, 250, NULL);
INSERT INTO player VALUES (4, 200, NULL);
INSERT INTO player VALUES (5, 175, NULL);

UPDATE   player
JOIN     (SELECT    p.playerID,
                    @curRank := @curRank + 1 AS rank
          FROM      player p
          JOIN      (SELECT @curRank := 0) r
          ORDER BY  p.points DESC
         ) ranks ON (ranks.playerID = player.playerID)
SET      player.rank = ranks.rank;

Result:

SELECT * FROM player ORDER BY rank;

+----------+--------+------+
| playerID | points | rank |
+----------+--------+------+
|        3 |    250 |    1 |
|        4 |    200 |    2 |
|        5 |    175 |    3 |
|        1 |    150 |    4 |
|        2 |    100 |    5 |
+----------+--------+------+
5 rows in set (0.00 sec)

UPDATE: Just noticed the that you require ties to share the same rank. This is a bit tricky, but can be solved with even more variables:

UPDATE   player
JOIN     (SELECT    p.playerID,
                    IF(@lastPoint <> p.points, 
                       @curRank := @curRank + 1, 
                       @curRank)  AS rank,
                    @lastPoint := p.points
          FROM      player p
          JOIN      (SELECT @curRank := 0, @lastPoint := 0) r
          ORDER BY  p.points DESC
         ) ranks ON (ranks.playerID = player.playerID)
SET      player.rank = ranks.rank;

For a test case, let's add another player with 175 points:

INSERT INTO player VALUES (6, 175, NULL);

Result:

SELECT * FROM player ORDER BY rank;

+----------+--------+------+
| playerID | points | rank |
+----------+--------+------+
|        3 |    250 |    1 |
|        4 |    200 |    2 |
|        5 |    175 |    3 |
|        6 |    175 |    3 |
|        1 |    150 |    4 |
|        2 |    100 |    5 |
+----------+--------+------+
6 rows in set (0.00 sec)

And if you require the rank to skip a place in case of a tie, you can add another IF condition:

UPDATE   player
JOIN     (SELECT    p.playerID,
                    IF(@lastPoint <> p.points, 
                       @curRank := @curRank + 1, 
                       @curRank)  AS rank,
                    IF(@lastPoint = p.points, 
                       @curRank := @curRank + 1, 
                       @curRank),
                    @lastPoint := p.points
          FROM      player p
          JOIN      (SELECT @curRank := 0, @lastPoint := 0) r
          ORDER BY  p.points DESC
         ) ranks ON (ranks.playerID = player.playerID)
SET      player.rank = ranks.rank;

Result:

SELECT * FROM player ORDER BY rank;

+----------+--------+------+
| playerID | points | rank |
+----------+--------+------+
|        3 |    250 |    1 |
|        4 |    200 |    2 |
|        5 |    175 |    3 |
|        6 |    175 |    3 |
|        1 |    150 |    5 |
|        2 |    100 |    6 |
+----------+--------+------+
6 rows in set (0.00 sec)

Note: Please consider that the queries I am suggesting could be simplified further.

Betweentimes answered 28/4, 2010 at 6:25 Comment(1)

Daniel, please see my comment to my own answer. – Arrington 29/4, 2010 at 6:20

Daniel, you have very nice solution. Except one point - the tie case. If tie happens between 3 players this update doesn't work properly. I changed your solution as following:

UPDATE player  
    JOIN (SELECT p.playerID,  
                 IF(@lastPoint <> p.points,  
                    @curRank := @curRank + @nextrank,  
                    @curRank)  AS rank,  
                 IF(@lastPoint = p.points,  
                    @nextrank := @nextrank + 1,  
                    @nextrank := 1),  
                 @lastPoint := p.points  
            FROM player p  
            JOIN (SELECT @curRank := 0, @lastPoint := 0, @nextrank := 1) r  
           ORDER BY  p.points DESC  
          ) ranks ON (ranks.playerID = player.playerID)  
SET player.rank = ranks.rank;

Caespitose answered 22/10, 2011 at 14:42 Comment(0)

EDIT: The update statement presented earlier did not work.

Although this is not exactly what you are asking for: You can generate the rank on the fly when selecting:

select p1.playerID, p1.points, (1 + (
    select count(playerID) 
      from Player p2 
     where p2.points > p1.points
    )) as rank
from Player p1
order by points desc

EDIT: Trying the UPDATE statement once more. How about a temporary table:

create temporary table PlayerRank
    as select p1.playerID, (1 + (select count(playerID) 
                                   from Player p2 
                                  where p2.points > p1.points
              )) as rank
         from Player p1;

update Player p set rank = (select rank from PlayerRank r 
                             where r.playerID = p.playerID);

drop table PlayerRank;

Hope this helps.

Arrington answered 28/4, 2010 at 6:6 Comment(10)

@Tom: No, it won't work. You'd get a You can't specify target table 'p1' for update in FROM clause, because of the p1 reference in the subquery. – Betweentimes 28/4, 2010 at 6:50

Thanks for the clarification Daniel. Since Col. Shrapnel pointed out that, strictly speaking, rank should be computed at select time, let me point out that my subselect should work for that purpose. – Arrington 28/4, 2010 at 7:4

@Tom: Yes, that subquery would work at SELECT time, but it still won't handle ties. The OP even tagged the question as 'tie'! :) – Betweentimes 28/4, 2010 at 7:12

I have a modified version of this query as update player g1 set g1.rank = 1 + ((SELECT count(*) from (select * from player) g2 where g2.points > g1.points)) I wonder if there is any optimization that can be done on it. – Multiversity 28/4, 2010 at 22:25

@Daniel: I don't see why my statement won't handle ties. Every player with the same points will get the same rank. If two players tie for rank 5, the next player(s) will be assigned rank 7. If that's not how "handling ties" is supposed to work, we have a different understanding of the term. – Arrington 29/4, 2010 at 6:19

@sammichy: And does your statement work? Is it slow or why do you want to optimize it? – Arrington 29/4, 2010 at 6:24

@Tom: It does in fact. I misinterpreted the query. +1 because it's a good option. However I think you should edit the answer, because it is results in an error as it is now. The select subqery works fine:

SELECT p1.points, p1.playerID, 1 + (select count(playerID) FROM Player p2 WHERE p2.points > p1.points) rank FROM player p1 ORDER BY rank;

– Betweentimes 29/4, 2010 at 6:37

@Daniel: Right you are. I edited my answer. Thanks for the +1. – Arrington 29/4, 2010 at 9:25

@Tom, my statement does work, but it is slow as it takes exponential time and I wanted to see if there was a way to make it run faster. – Multiversity 29/4, 2010 at 15:46

@Tom - is there way to convert your select clause into an update statement for the whole table. I'm having trouble because of 'can't specify target table for update in FROM clause' ... – Multiversity 29/4, 2010 at 16:25

According to Normalization rules, rank should be evaluated at SELECT time.

Fabrikoid answered 28/4, 2010 at 6:8 Comment(1)

Yes, but this is primarily a lookup table where rank is calculated periodically and I do not want to run this every time a user logs in. – Multiversity 28/4, 2010 at 14:31

Recommended topics

Hot tags