MySQL ranked results with GROUP BY

Question

I have this table:

id leaderboard_id player_id score
 1              1        3 5001
 2              1        2  501
 3              1        4  490
 4              2        3 1001
 5              2        2  110

I want to get the player_id = 3 rank on both leaderboards (leaderboard_id = 1 and 2).

I tried many options with no success, they give me rank 1 in leaderboard 1, rank 2 in leaderboard 2, when they both should be rank 1.

Last code that gave me those results is:

SELECT * FROM ( SELECT s.*, @rank := @rank + 1 rank FROM ( SELECT leaderboard_id, player_id, score FROM leaderboards t GROUP BY leaderboard_id ) s, (SELECT @rank := 0) init ORDER BY score DESC ) r WHERE player_id = 3

... with this result:

If anyone can point to a solution, it would be very appreciated.

Thank you

Answer 1

In MySql 8 you could use window functions like ROW_NUMBER or DENSE_RANK for this.

In MySql 7 you can emulate those window functions using variables.

In your case you would want a ranking per leaderboard.
So it needs to check if the leaderboard changed.
The ORDER BY in the sub-query does matter for that calculation.

Sample data:

-- Test Table
drop table if exists test_leaderboards;
create table test_leaderboards (
  id int primary key auto_increment,
  leaderboard_id int not null,
  player_id int not null,
  score int not null
);

-- Sample Data
insert into test_leaderboards (leaderboard_id, player_id, score) values
(1, 3, 3333),
(1, 2, 2222),
(1, 4, 4444),
(2, 3, 3333),
(2, 2, 2222),
(2, 1, 1111);

Query:

SELECT leaderboard_id, player_id, score, rank
FROM 
( 
  SELECT leaderboard_id, player_id, score, 
    CASE 
    WHEN leaderboard_id = @prev then @rank := @rank + 1
    -- Remark: a variable assignement is always true
    WHEN @prev := leaderboard_id then @rank := 1
    END AS rank 
  FROM 
  (
       SELECT leaderboard_id, player_id, score
       FROM test_leaderboards
       ORDER BY leaderboard_id ASC, score DESC
  ) data
  CROSS JOIN (SELECT @rank := null, @prev := null) AS init
) r 
WHERE player_id = 3;

Result:

leaderboard_id  player_id   score   rank
--------------  ---------   -----   ----
1               3           3333    2
2               3           3333    1