SQL SELECT not behaving as expected when using MIN() and GROUP BY

Question

I have a simple online leaderboard which also encodes replays in a string. Although the leaderboard stores every laptime reported (currently), the retrieval php just returns the best times for each unique player, thus:

SELECT driver
     , MIN(time)
     , track
     , replay 
  FROM Laptimes 
 WHERE track = '$track'
 GROUP 
    BY driver 
 ORDER 
    BY MIN(time) ASC 
 LIMIT 10

This correctly reports the fastest laptime, but does NOT select the replay associated with that laptime.

Instead you just get the first replay submitted for that driver.

I'm 100% sure the replays are correctly stored in the database, because if I remove the MIN() I get every laptime by every player, and can watch each replay without any problem.

I just can't seem to convince SQL to give me the replay associated with the minimum laptime.

This must be an older version of mysql you are on as any other RDBMS and any newer version of Mysql (5.7 or newer) is going to throw an error if you don't include any non-aggregated column in your group by statement. You either need to aggregate your column with an aggregate formula in your select clause OR that column must be in your group by clause. Otherwise you get strange nonsense results like you are seeing. — JNevill, Oct 14 '20 at 21:54

GMB · Accepted Answer · 2020-10-14T22:01:14.097

You want entire rows, so you need to filter rather than aggregate. A simple approach uses a correlated subquery:

select l.*
from laptimes l
where
    track = ? 
    l.time = (select min(l1.time) from laptimes l1 where l1.driver = l.driver and l1.track = l.track)

Note that, as commented by JNevill, your original query is not valid standard SQL, because the select and group by clauses are not consistent. MySQL might tolerate it (if you have option ONLY_FULL_GROUP_BY disabled, which is the default in old versions), but then you get an arbitrary values in non-aggregated columns that are not present in the group by clause. This might be simpler to understand when the query is written as follows (which is equivalent to your original code - and is valid MySQL code):

SELECT driver, MIN(time), ANY_VALUE(track), ANY_VALUE(replay) 
FROM Laptimes 
WHERE (track='$track') 
GROUP BY driver 
ORDER BY MIN(time) ASC LIMIT 10

Note #2: use prepared statements! Do not mungle parameters into the query string - this is both inefficient and unsafe.

That worked perfectly, thank you! Funnily enough, I got the query I was using from another SO question about leaderboards :/ hey ho. — Peeling, Oct 14 '20 at 22:10

SQL SELECT not behaving as expected when using MIN() and GROUP BY

1 Answers1