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
<strong>UPDATE</strong>: Solved (or so I though at the time :s)
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
<strong>UPDATE II</strong>: not solved after all :S
So happy that the proper Highscores were showing up in the ranking, I didn't realise that, actually, with this query <em>All the scores from each user are showing up</em>, instead of returning only the best score of each user. Here I go again, in the search of the most simple "return highscores" SQL Query using userID
<strong>UPDATE III</strong>: I think the answer might come from <a href="https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column" rel="nofollow">this place</a> so I put this together, and again, it works in my local test and fails when a apply it on my server
SELECT a.id, a.timestamp, a.name, a.score, a.color, a.flower, a.user_id FROM games_table a INNER JOIN ( SELECT id, MAX(score) score FROM games_table GROUP BY user_id ) b ON a.id = b.id AND a.score = b.score ORDER BY a.score DESC LIMIT 100
Still investigating, the piñata way ;)
<strong>UPDATE IV</strong>: I think I have it (but I won't it is done before I'm sur) In the mean time this is what I found. NOPE, ANOTHER FAILED ATTEMPT :(
I tried my queries directly inside PHPmyAdmin
Found out that that query
SELECT timestamp, name, MAX(score), color, flower, device_id FROM WorldFlowers_table GROUP BY device_id ORDER BY score DESC LIMIT 100;
returned an error message like this: "Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available."
So I added "id," at the beginning of line #2
and now it looks like I get the result I was looking for I did some test but I will make sur it works to mark the question as "answered"
Did not work, now I'm looking at <a href="https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column?rq=1" rel="nofollow">this thread</a> without much hope
The only thing I gained today is that I test the queries directly into phpmyadmin
if there is anyone out there <strong>HELP !</strong>
<strong>UPDATE V</strong>: someone called scaisEdge gave me this answer and, at least until now, it seems to work. Waiting for more testing to say "answered"
select * from WorldFlowers_table where (device_id, score) in ( select device_id, max(score) FROM WorldFlowers_table GROUP BY device_id ORDER BY score DESC ) ORDER BY score DESC LIMIT 100
it works !! :)
and the answer was given to me , like I said above, by scaisEdge, in another "instance" of this question that I posted here <a href="https://stackoverflow.com/questions/39833606/mysql-get-top-100-scores-query-is-turning-me-crazy" rel="nofollow">mySQL "get TOP 100 scores" query is turning me crazy</a>Answer1:
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;Answer2:
Have you tried to use the sum?
$sql = "SELECT SUM(score) as sum_score FROM WorldGames_table GROUP BY user_id ORDER BY SUM(score) DESC LIMIT 100";Answer3:
I don't know if I understand you well but maybe you need to write your query like that:
$sql = "SELECT WorldGames_table.*, MAX(score) as score FROM WorldGames_table GROUP BY user_id ORDER BY score DESC LIMIT 100";