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.

Answer 1

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.