RichGG - 10 months ago 50
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

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;