sorting - ORDER BY date and time BEFORE GROUP BY name in mysql

Question

i have a table like this:

name    date         time
tom | 2011-07-04 | 01:09:52
tom | 2011-07-04 | 01:09:52
mad | 2011-07-04 | 02:10:53
mad | 2009-06-03 | 00:01:01

i want oldest name first:

SELECT * 
ORDER BY date ASC, time ASC 
GROUP BY name

(->doesn't work!)

now it should give me first mad(has earlier date) then tom

but with GROUP BY name ORDER BY date ASC, time ASC gives me the newer mad first because it groups before it sorts!

again: the problem is that i can't sort by date and time before i group because GROUP BY must be before ORDER BY!

Answer 1

Another method:

SELECT * 
FROM (
    SELECT * FROM table_name
    ORDER BY date ASC, time ASC 
) AS sub
GROUP BY name

GROUP BY groups on the first matching result it hits. If that first matching hit happens to be the one you want then everything should work as expected.

I prefer this method as the subquery makes logical sense rather than peppering it with other conditions.