mysql before 时间,ORDER BY日期和时间BEFORE GROUP BY名称在mysql中

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!

解决方案

Another method:

SELECT *

FROM (

SELECT *

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.