Update the rank in a MySQL Table

Question

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?

Answer 1

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. Cheers, Tom

Answer 2

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

Answer 3

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:

• SQL: Ranking without self join
• Stack Overflow: Create a Cumulative Sum Column in MySQL

---

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.