Question 63932 in PhpMyAdmin Question posted in
The official documentation can be found here.

MySQL Fetch highest value by key and then add rank to it – Phpmyadmin

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:
enter image description here

But I do know that this player has played multiple times and is therefore multiple times in the scoreboard. As seen here:
enter image description 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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search