MySQL Fetch highest value by key and then add rank to it-CodePudding

I'm trying to create a mysql query that looks through my scoreboard for a given playerid and then finds their highest score and then adding a rank to that score. I've come quite close to what I'm trying to achieve with this code:

SELECT PlayerUUID, `iGamescore` as score, FIND_IN_SET( iGamescore, (    
SELECT GROUP_CONCAT( iGamescore
ORDER BY iGamescore ASC ) 
FROM crystm_gameScoreboard )
) AS rank
FROM crystm_gameScoreboard
WHERE PlayerUUID =  '4c8984f3-651a-48bc-ad1a-879624380fab'
LIMIT 1

Returns:

But I do know that this player has played multiple times and is therefore multiple times in the scoreboard. As seen here:

So the issue here is that yes, it does find the player and gives the rank correctly.. However, since it exists multiple times, it saves the very first score instead of the highest score. How would I fix my query to correct for this or would you instead of creating a new score every time they create a highscore for themselves, just update their previous record in the scoreboard?

Thanks in advance

CodePudding user response：

To get the highest score you need a GROUP BY:

SELECT 
   PlayerUUID, 
   MAX(`iGamescore`) as score
   RANK() OVER (ORDER BY MAX(`iGamescore`) DESC) as Rang
FROM crystm_gameScoreboard
GROUP BY PlayerUUID 
ORDER BY 3 ASC

The order by 3 ASC makes the list sorted by rank

CodePudding user response：

This post solved it: MySQL - Rank user amongst list of top high-scores

Had everything I was looking for. I dont know why I could not find this post but when searching, keywords are important haha.

Thanks for inputs tho.