>>>> 2011/09/27 05:32 -0700, supr_star >>>> Here's a simple query: select name,status,min(dt), max(dt),count(*) from mytable group by name I need an efficient way to get this data, along with the status of the row with the max(dt). 'status' is not in the group by, so I get apparently random statuses. Is there any way to do this without a table join? Or I guess the real question is: What is the most efficient way of accomplishing this? <<<<<<<< I cannot answer the real question, but there is this:
select name,(SELECT status FROM mytable AS x WHERE x.name = mytable.name AND x.dt = max(mytable.dt)) AS status, min(dt), max(dt),count(*) from mytable group by name I will not vouch that this is more efficient than joining. (Surely there are enough of this that this is entitled to a special SQL construct.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
