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

[Max Waterman]

laptop alias Wrote:
-------------------------------------------------------
> This one gets asked every day;
>
> SELECT t1.* FROM results t1
> LEFT JOIN results t2
> ON t1.athleteid = t2.athleteid
> AND t2.raceTime < t1.raceTime
> WHERE t2.resultid IS NULL;

Ah yes, that's better. In full, it would seem to be :

SELECT
    r1.*,
    a.*
  FROM
    results           AS r1
    LEFT JOIN results AS r2 ON ( r1.athleteID=r2.athleteID AND r1.time>r2.time )
    JOIN athletes     AS a  ON ( r1.athleteID=a.athleteID )
  WHERE
    r2.resultID IS NULL
  ORDER BY r1.time ASC;

Max.