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

User · Answer

This is not the exact answer  but this might be helpful for the people looking to solve some problem with the approach of ordering row before group by in mysql   I came to this thread  when I wanted to find the latest row which is order by date desc but get the only one result for a particular column type  which is group by column name     One other approach to solve such problem is to make use of aggregation   So  we can let the query run as usual  which sorted asc and introduce new field as max doc  as latest doc  which will give the latest date  with grouped by the same column   Suppose  you want to find the data of a particular column now and max aggregation cannot be done  In general  to finding the data of a particular column  you can make use of GROUP CONCAT aggregator  with some unique separator which can t be present in that column  like GROUP CONCAT string SEPARATOR      as new column  and while you re accessing it  you can split explode the new column field   Again  this might not sound to everyone  I did it  and liked it as well because I had written few functions and I couldn t run subqueries  I am working on codeigniter framework for php   Not sure of the complexity as well  may be someone can put some light on that   Regards

User · Answer

Use a subselect   select name  date  time from mytable main where date   time    select min date   time  from mytable where name   main mytable  order by date   time

User · Answer

This worked for me   SELECT   FROM your table WHERE id IN       SELECT MAX id      FROM your table     GROUP BY name

User · Answer

I had a different variation on this question where I only had a single DATETIME field and needed a limit after a group by or distinct after sorting descending based on the datetime field  but this is what helped me   select distinct  column  from  select column from database table order by date column DESC  as hist limit 10   In this instance with the split fields  if you can sort on a concat  then you might be able to get away with something like   select name date time from  select name from table order by concat date     time  ASC  as sorted   Then if you wanted to limit you would simply add your limit statement to the end   select name date time from  select name from table order by concat date     time  ASC  as sorted limit 10

User · Answer

work for me mysql select   from  SELECT number max date added  as datea FROM  sms chat group by number  as sup order by datea desc

User · Answer

In Oracle  This work for me  SELECT name  min date   min time      FROM table name GROUP BY name

User · Answer

Another way to solve this would be with a LEFT JOIN  which could be more efficient  I ll first start with an example that considers only the date field  as probably it is more common to store date   time in one datetime column  and I also want to keep the query simple so it s easier to understand   So  with this particular example  if you want to show the oldest record based on the date column  and assuming that your table name is called people you can use the following query   SELECT p   FROM people p LEFT JOIN people p2 ON p name   p2 name AND p date  gt  p2 date WHERE p2 date is NULL GROUP BY p name   What the LEFT JOIN does  is when the p date column is at its minimum value  there will be no p2 date with a smaller value on the left join and therefore the corresponding p2 date will be NULL  So  by adding WHERE p2 date is NULL  we make sure to show only the records with the oldest date   And similarly  if you want to show the newest record instead  you can just change the comparison operator in the LEFT JOIN   SELECT p   FROM people p LEFT JOIN people p2 ON p name   p2 name AND p date  lt  p2 date WHERE p2 date is NULL GROUP BY p name   Now  for this particular example where date time are separate columns  you would need to add them in some way if you want to query based on the datetime of two columns combined  for example   SELECT p   FROM people p LEFT JOIN people p2 ON p name   p2 name AND p date   INTERVAL TIME TO SEC p time  SECOND  gt  p2 date   INTERVAL TIME TO SEC p2 time  SECOND WHERE p2 date is NULL GROUP BY p name   You can read more about this  and also see some other ways to accomplish this  on the The Rows Holding the Group-wise Maximum of a Certain Column page

User · Answer

I think this is what you are seeking    SELECT name  min date  FROM myTable GROUP BY name ORDER BY min date    For the time  you have to make a mysql date via STR TO DATE    STR TO DATE date         time    Y- m- d  h  i  s     So    SELECT name  min STR TO DATE date         time    Y- m- d  h  i  s    FROM myTable GROUP BY name ORDER BY min STR TO DATE date         time    Y- m- d  h  i  s

User · Answer

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

User · Answer

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

[mysql] ORDER BY date and time BEFORE GROUP BY name in mysql

Examples related to mysql

Examples related to sorting

Examples related to group-by

Examples related to sql-order-by