Ordering and grouping in php/MySQL

Question

I have a table with user_id and lap_time and I'm running the following query:

SELECT `Lap`.`id`, `Lap`.`user_id`, `Lap`.`lap_time` 
FROM `laps` AS `Lap` 
WHERE `Lap`.`lap_time` < 57370 
GROUP BY `Lap`.`user_id` 
ORDER BY `Lap`.`lap_time` ASC

I am trying to get the all the laps that are quicker than X but only unique users.

The query above doesn't return the users top lap, its like I need to order the GROUP BY if that makes sense?

Users can have many laps, so there could be 10 laps faster than X but they're all by same user, so I just want the top lap for that user.

Hope that makes sense and someone can help!

Answer 1

I think you should use a SELECT COUNT(*) AS some_field, group by that field and use MAX to get the top-result per user.

Answer 2

When dealing with similar situations I've added a sub-query to ensure there isn't a faster time for that user:

WHERE Lap.lap_time < 57370 AND NOT EXISTS (select 1 from laps where laps.user_id = Lap.user_id and laps.lap_time < Lap.lap_time)

Answer 3

This query will give you the fastest lap for each user_id(where lap_time is below 57370) and match it up with the correct record id. and in general, joins have a bit better performance with mysql than sub selects do.

select l2.id,
       l1.user_id,
       l1.lap_time
  from lap l1
  inner join lap l2
     on l1.id = l2.id
  where l1.lap_time < 57370
  group by l1.user_id
  having min(l1.lap_time);