On gists

Equal rank with same values

MySql MySql tricks

example.sql Raw #

id   game_id   game_rank   score   team_id
-------------------------------------------
5    1         1           15      1
4    1         2           25      2
1    1         3           40      3
3    1         3           40      4
2    1         4           55      5
7    2         0           0       1
6    2         0           0       2


#1
SET @lastscore = 0;
SET @ordering = 0;

    UPDATE game_scores 
      SET
        game_rank = IF(score = @lastscore, @lastscore, (@ordering := @ordering + 1))
        , score = (@lastscore := score)
    WHERE game_id = 1
    ORDER BY score;
    
    
#2
UPDATE game_scores 
  CROSS JOIN ( SELECT @lastscore:=0, @ordering:=0) AS parameter
  SET
    game_rank = IF(score = @lastscore, @lastscore, (@ordering := @ordering + 1))
    , score = (@lastscore := score)
WHERE game_id = 1
ORDER BY score;


#3
SET @lastscore := 0;
SET @ordering := 0;

UPDATE game_scores SET 
IF(@lastscore = score, @ordering, @ordering := @ordering + 1),
game_rank = @ordering,
@lastscore := score
WHERE game_id = 1
ORDER BY score;