Query Question: finding a min value with a group

[spud101]

I have a race database that contains a table with a field for a lap time and another for the track where the lap was run, and a driver_id field for the driver. I'm trying to use a query to pull the fastest lap time for each track. I can get the info like this :

select min(qualifytime),Track
from points
group by Track

but i also need to see the driver who ran the lap.
If I add the driver_id to the query, I also have to add it to the group by and I get every drivers time, not just the driver with the fastest lap at each track.

I tried a where clause with a subquery like
where qualifytime = (select min(qualifytime) from points)
but that doesn't give a min time for each track.

Any help appreciated

 

[MeanGreen]

Try this:

select b.track,b.qualtime,a.riderid from points a join (
select track,min(qualtime) qualtime from points
group by track) b
on a.track = b.track and a.qualtime = b.qualtime


Hope this helps.

 

[spud101]

Very Good!
Now all I have to do is pick it apart and try to understand how it works.

Thanks

 

[MeanGreen]

The statement after the join basically creates an alias "table" with appropriate fields and then you join them to your original data to get your results.

Hope this helps.