RichGG RichGG - 2 months ago 9
MySQL Question

most simple "return highscores" SQL Query using userID

High there

working at a minimal game on Android I get lost when it comes to sql queries.

I'm trying to get a top 100 ranking list and it almost works but not quite

I am using this query

$sql = "SELECT * FROM WorldGames_table GROUP BY user_id ORDER BY score DESC LIMIT 100";


the thing is that it returns a list of the top 100 scores, in the proper order and containing only 1 score of each user. But it is not always the best score of the users that is shown.

I am getting completely confused by the way complexes queries are "organised", that is why I am looking for a simple query

cheers

UPDATE: Solved

this is the line that returns what i was looking for :D

SELECT WorldGames_table .*, MAX(score) as score FROM WorldGames_table GROUP BY id, device_id ORDER BY score DESC LIMIT 100;


Thanks to AT-2016 and to every one who gave a hand :D

Answer

Try to use MAX() to get the highest values:

SELECT MAX(Values) FROM WorldGames_table 
GROUP BY user_id ORDER BY score DESC LIMIT 100

Try the below:

SELECT *
FROM Table JOIN
    (SELECT MIN(Score) as Values -- Or use MAX()
      FROM (SELECT Score
            FROM Table
            ORDER BY Score
            LIMIT 100
           ) Table2
    ) Table2
ON Table.Score >= Table2;