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!

Peter Bailey · Accepted Answer · 2009-12-03 16:16:49Z

1

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;

edited Dec 3, 2009 at 16:16

answered Dec 3, 2009 at 15:06

Peter Bailey

106k32 gold badges186 silver badges206 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

designvoid Over a year ago

Thanks for that, slight issue tho, the id thats returned is not the id of the MIN lap_time... any way round this?

designvoid Over a year ago

I was correct, whilst this does return the lowest lap time for that user, the lap.id in the row is NOT the lap.id of the lowest lap time and I really need that.

user224114 · Accepted Answer · 2009-12-03 19:27:23Z

0

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

answered Dec 3, 2009 at 19:27

user224114

Comments

Ben · Accepted Answer · 2009-12-03 14:48:36Z

0

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.

answered Dec 3, 2009 at 14:48

Ben

11.3k19 gold badges80 silver badges140 bronze badges

Comments

Ickmund · Accepted Answer · 2009-12-03 14:50:40Z

0

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)

answered Dec 3, 2009 at 14:50

Ickmund

6411 gold badge5 silver badges9 bronze badges

Collectives™ on Stack Overflow

Ordering and grouping in php/MySQL

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related