MySql update order/rank colum but allow for duplicate ranks

I have a table with game scores for each team like this:

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

I want to automatically sort the teams by score and assign them a game rank (first place, second place...) according to those scores. I would like to allow for ties based on duplicate scores, and only increment the rank if the score is different.

This is my desired result.

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

I have the following query so far, but it does not allow for duplicate game ranks.

SET @lastscore = 0;
SET @ordering = 0;

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

Can anyone help me handle the duplicate scores?

Answers


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;

You also can do it in one query:

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;

you should check the condition every time when score are not same with previous score then @ordering increase by 1 other wise @ordering same as current @ordering

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;

Need Your Help

How i can Set an existing LiveConnectSession to the LiveConnectClient?

ios iphone ipad onedrive

i'm trying to implement a multi connexion on my ios app so i can add two oneDrive account on my app , so i need to save the LiveConnectSession and set it to the current LiveConnectClient but the s...