CODEWITHSUNDEEP
phpmysqlsql
user1022585
user1022585

Reputation: 13651

SQL recordset confusing results

I have a leaderboards for my game which displays the top rankers by map in PHP from a mySQl database; 

SELECT name, time, MAX(score) as max_score 
FROM leaderboards 
WHERE name != '' AND hidden=0 AND map='" .$currMap. "' AND score > 0 
GROUP BY name 
ORDER BY score DESC, time ASC LIMIT ".($get_page*10).", 10

I'm paging the results shown on the site in 10s.

Theres are several people on there who have multiple scores. Their records don't show up where they should on the list. Instead they shows up way further down in POSITION of their lowest score, but with the VALUE of their highest. So it's not sorting them correctly.

Any advice?

Upvotes: 1

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

It is not pulling up the time associated with that score. It is pulling up an arbitrary record. Your query is using a MySQL extension, where you have a column in the select (time) that is not in the group by. As *explicitly` stated in the documentation, you get a value from an indeterminate row.

One way you can get what you want is with the substring_index()/group_concat() trick:

SELECT name,
       SUBSTRING_INDEX(GROUP_CONCAT(time ORDER BY score DESC), ',', 1) as TimeAtMaxScore,
       MAX(score) as max_score 
FROM leaderboards 
WHERE name <> '' AND hidden = 0 AND map='" .$currMap. "' AND score > 0 
GROUP BY name 
ORDER BY score DESC, time ASC LIMIT ".($get_page*10).", 10;

(As a note: I used version 5.6 of the documentation, because it is more explicit about what happens. Version 5.7 does a better job of explaining what functional dependency is, which is the basis for this extension. The ANSI standard does allow it under some circumstances.)

EDIT:

You have the same problem in the order by. You can do:

ORDER BY max_score DESC, TimeAtMaxScore ASC
LIMIT . . .

Upvotes: 1

Related Questions