Ordering and grouping in php/MySQL

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开发者_如何学Go 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!

The query you have will return every lap for every user that's lower than 57370. If you only want each user's best lap, just add a MIN around the lap_time

SELECT `Lap`.`id`, `Lap`.`user_id`, min(`Lap`.`lap_time`) as `best_lap_time`
FROM `laps` AS `Lap` 
WHERE `Lap`.`lap_time` < 57370 
GROUP BY `Lap`.`user_id` 

Also, you query formatting is pretty overkill. No reason to make a table alias when there's only one table in the query. Unless you copy-pasted this from some program that generated it for you.

EDIT

Sorry, you are right - the min time won't always match up with the ID. This should do the trick

select l.id
     , l.user_id
     , (select min(lap_time)
          from lap
         where lap_time < 57370 
           and user_id = l.user_id
       ) as best_lap_time
 group by l.user_id
having best_lap_time is not null
 order by best_lap_time asc;

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);

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.

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)