Re: GROUP BY, MIN() row ordering problems!

[Max Waterman]

oh, i see. yes, you need to order the data first, probably with a subquery, like this :

SELECT *, MIN(r.time) FROM (select * from results as r order by r.time asc) AS r JOIN athletes AS a USING (athleteID) GROUP BY athleteID ORDER BY r.time ASC;
+-----------+----------+-------+----------+----------+------------+--------+------+---------+-------------+--------+-------------+------------+-----------+-----------+--------+------------+--------+-------------+
| athleteID | resultID | event | ageGroup | time     | distHeight | record | wind | placing | competition | in_out | venue       | date       | nameFirst | nameLast  | gender | DOB        | clubID | MIN(r.time) |
+-----------+----------+-------+----------+----------+------------+--------+------+---------+-------------+--------+-------------+------------+-----------+-----------+--------+------------+--------+-------------+
|      3450 |       30 | 100m  | SM       | 00010.15 | 000000     |        | 1.6  | 1       | Test Meet   | Out    | Auckland    | 2008-01-01 | Gavin     | LOVEGROVE | M      | 1967-10-21 |    151 | 00010.15    | 
|       822 |       26 | 100m  | SM       | 00010.20 | 000000     |        | 1.1  | 1       | Battle      | Out    | Hamilton    | 2008-02-01 | James     | DOLPHIN   | M      | 1983-06-17 |    151 | 00010.20    | 
|       825 |       27 | 100m  | SM       | 00010.56 | 000000     |        | 1.1  | 1       | New Meet    | Out    | Auckland    | 2008-01-01 | Chris     | DONALDSON | M      | 1975-05-26 |    151 | 00010.56    | 
|       199 |       23 | 100m  | SM       | 00010.59 | 000000     |        | 0.9  | 2       | Battle      | Out    | North Shore | 2008-01-02 | Craig     | BEARDA    | M      | 1982-06-17 |    151 | 00010.59    | 
+-----------+----------+-------+----------+----------+------------+--------+------+---------+-------------+--------+-------------+------------+-----------+-----------+--------+------------+--------+-------------+

Does that make more sense? (at least the 'time' col is the same as the 'MIN(r.time)' col now).

Max.