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.

Answer 2

As I am not allowed to comment on user1908688's answer, here a hint for MariaDB users:

SELECT * FROM (      SELECT *      ORDER BY date ASC, time ASC      LIMIT 18446744073709551615      ) AS sub GROUP BY sub.name 

https://mariadb.com/kb/en/mariadb/why-is-order-by-in-a-from-subquery-ignored/