selecting rows with aggregate functions

subwayman

I have a table with race results in:

ResultID - Auto-increment unique result record ID
MeetID - Foreign Key - ID of Meet at which race was run
EventID - Foriegn Key - ID of race within Meet
Athlete - Foriegn Key - ID of Athelete owning this result
Distance - distance ran in this event
Time - race time for this result (0 if disqualified)

I want to get the Personal Bests out of this table - selecting the results which give the fastest time for each distance. This is for a report which will give athletes their PB times and when and where they were set.

I have a query so far:

SELECT *,MIN(time) as PB
FROM results
WHERE time > 0
GROUP BY athlete, distance
ORDER BY athlete, distance

This returns the required PB value but the rest of the columns returned are not from the record with the PB time in it.

How do I get the query to return the actual rows with each PB in it?

Thanks

bastien

Does your db support subselects? With that it would be easier

else you could try the 'having' clause

SELECT *,MIN(time) as PB
FROM results
WHERE time > 0
GROUP BY athlete, distance
having time = min(time)
ORDER BY athlete, distance

subwayman

Thanks for taking the time to help.

I Use MYSQL. How would I use a sub select? I am a relative novice with sql.

Here is what I have now:

SELECT stroke, distance, time, min(time)
FROM results
WHERE memID = 407 and time > 0
GROUP BY stroke, distance
HAVING time = min(time)
ORDER BY stroke, distance

MemID is the key for individual atheletes. 0 times are when the athlete was disqualified during the race.

Using the HAVING clause doesn't help too much. Without the HAVING clause the query returns :

stroke distance time min( time )
Free 20 1588 1225
Free 25 1977 1687
Free 40 3733 2982
Free 50 4154 3771
Free 100 8844 8844
Free 200 21365 19083
Back 20 2075 1646
Back 25 2333 2016
Back 40 4829 3645
Back 50 4538 4287
Back 100 11513 11513
Back 200 22150 20494
Breast 20 2579 2579
Breast 25 2742 2742
Breast 40 5364 3913
Breast 50 5716 4910
Breast 100 11400 11400
Breast 200 28585 23387
Fly 20 1840 1840
Fly 25 2554 2082
Fly 40 5292 4177
Fly 50 5164 5024
IM 25 10760 9972

But with the HAVING clause I get:

stroke distance time min( time )

Free 100 8844 8844
Back 100 11513 11513
Breast 20 2579 2579
Breast 25 2742 2742
Breast 100 11400 11400
Fly 20 1840 1840

Lars_Berg

SELECT stroke, distance, min(time)
FROM results
WHERE memID = 407 
    and time > 0
GROUP BY stroke, distance
ORDER BY stroke, distance

All columns in the select list that are not part of an aggregate function should be present in the group by clause.

subwayman

Thanks. But this just gives me the PB's. I need to identify the actual record which has the best time so I can get the date and meet details.

I there any way I can get another column in there?

Lars_Berg

SELECT *
FROM results r
WHERE time > 0
and time in 
   (select min(time)
        from results mr
      where mr.athlete = r.athlete
          and mr.stroke = r.stroke
          and mr.distance = r.distance)
ORDER BY athlete, distance