How can my MySQL query giving the rank of all games just give the rank of each players top game?

Question

SELECT 
1+(SELECT count(*) from games a WHERE a.score > g.score) as rank,
score, p.leaderboard_initials, p.operating_system
FROM games g
inner join players p on (p.google_id = g.google_id)
ORDER BY score DESC
LIMIT 0, 100;

Returns a leaderboard featuring EVERY game for a player.

I need to return a leaderboard of where only the BEST scoring game of each player is listed. So 44rhh would still be on top, followed by DMC but then BMO would be in third place.

I know the solution should be easy but I just can't think how to do it!

Answer 1

you could select max score group by leaderboard_initials

you could select max score group by leaderboard_initials

set @rank=0;
select (@rank:=@rank+1) rank , leaderboard_initials, max(score)  max_score  
from  (
    SELECT 
    1+(SELECT count(*) 
    from games a WHERE a.score > g.score) as rank,
    score, p.leaderboard_initials, p.operating_system
    FROM games g
    inner join players p on (p.google_id = g.google_id)
    ORDER BY score DESC
    LIMIT 0, 100;
) t  
group by leaderboard_initials 
order by max_score DESC