GROUP BY, MIN() row ordering problems!

[Gavin Lovegrove]

Hi all - would appreciate any help you may have ...

I am on MySql Ver.4.1

I am currently putting together a sports results/rankings database web application and are experiencing problems with the 'GROUP BY' MIN() function.

The two tables I am referring to:

TABLE 1 = athletes
athleteID (PK), nameFirst, nameLast, gender, DOB, club

TABLE 2 = results
resultID (PK), athleteID, event, ageGroup, raceTime, placing, competition, date

Some simple sample data for TABLE 1 (results):
resultID, athleteID, event, raceTime, placing, competition, date
1 | 182 | 100m | 10.50 | 1 | League | 2008-12-12
2 | 12 | 100m | 10.60 | 2 | Nationals | 2008-11-15
3 | 268 | 100m | 10.40 | 1 | League | 2008-06-05
4 | 9 | 100m | 11.20 | 4 | Local | 2008-07-03
5 | 12 | 100m | 11.60 | 3 | League | 2008-12-01
6 | 182 | 100m | 10.10 | 1 | Challenge | 2008-10-19


I wish to create a query that will return the fastest time for each athlete, and return only one unique result for each athlete (athleteID).

So far I have this:

SELECT *, MIN(raceTime)
FROM athletes, results
WHERE results.athleteID = athletes.athleteID
GROUP BY resultID
ORDER BY raceTime ASC

This works great, but it returns more than one record for each athlete:

resultID, athleteID, event, raceTime, placing, competition, date
6 | 182 | 100m | 10.10 | 1 | Challenge | 2008-10-19
3 | 268 | 100m | 10.40 | 1 | League | 2008-06-05
1 | 182 | 100m | 10.50 | 1 | League | 2008-12-12
2 | 12 | 100m | 10.60 | 2 | Nationals | 2008-11-15
4 | 9 | 100m | 11.20 | 4 | Local | 2008-07-03
5 | 12 | 100m | 11.60 | 3 | League | 2008-12-01

I am after this result set (fastest raceTime for unique athleteID):

resultID, athleteID, event, raceTime, placing, competition, date
6 | 182 | 100m | 10.10 | 1 | Challenge | 2008-10-19
3 | 268 | 100m | 10.40 | 1 | League | 2008-06-05
2 | 12 | 100m | 10.60 | 2 | Nationals | 2008-11-15
4 | 9 | 100m | 11.20 | 4 | Local | 2008-07-03

I also tried this:

SELECT *, MIN(raceTime)
FROM athletes, results
WHERE athletes.athleteID = results.athleteID
GROUP BY results.athleteID
ORDER BY raceTime ASC

This produces the right athleteID with the right raceTime, but the other fields are showing incorrect values:

resultID, athleteID, event, raceTime, placing, competition, date
6 | 182 | 100m | 10.10 | 1 | League | 2008-12-12
3 | 268 | 100m | 10.40 | 1 | League | 2008-06-05
2 | 12 | 100m | 10.60 | 3 | League | 2008-12-01
4 | 9 | 100m | 11.20 | 4 | Local | 2008-07-03

Any suggestions would be graetefully accepted.

Gavin



Edited 1 time(s). Last edit at 01/28/2008 12:08AM by Gavin Lovegrove.