Order inside Group By in Mysql statement

Question

I have a table with the following structure...

--------------------------------
id | state_abbr | poll_date
1       GA         2010-01-01
2       GA         2011-01-01
3       NC         2011-02-01
-------------------------------

I need a query that will bring back the latest poll_date for each state. I am using group by but I can't find out how to order within that to bring back the latest poll_date. It just brings back the first poll_date for each state. So GA would be 2010-01-01 and the NC would be 2011-02-01 and the order by would then order on those values. What can I do to get around this??

If you just need the poll date, you can get it with `SELECT state_abbr, MAX(poll_date) FROM states GROUP BY state_abbr`. If you need the entire row, see this question: http://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column — Sam Dufel, May 31 '12 at 01:01
Here is the query.....but I want the whole row,not just the max poll_date. SELECT state_abbr, MAX(poll_date) as poll_date FROM 2012_early_polls e GROUP BY state_abbr — Brian, May 31 '12 at 01:07

Harald Brinkhof · Accepted Answer · 2012-05-31T01:30:21.433

How about

SELECT id, state_abr, poll_date FROM <yourTableName> t INNER JOIN (SELECT MAX(poll_date) max_date, id, state_abbr FROM <yourTableName> GROUP BY state_abr) p ON t.state_abbr = p.state_abbr && t.poll_date = p.max_date;

Since you can't group on more than 1 thing, this way you perform the group, collect the aggregate max date and then use that data to select the final corresponding row(s).

Edit: removed DISTINCT ROW brain lapse, sorry, its what i get for being up so late. :)

score 0 · Answer 2 · answered May 31 '12 at 01:34

Mysql kung fu to the rescue:

select * from
(select * from polls order by state_abr, poll_date desc) x
group by state_abr

As mental as it looks, and even though it isn't even valid SQL in other databases, this works.

mysql has special functionality whereby it allows group by without aggregating non-group by columns, in which case it returns the first row encountered for the specified group.

If you select from an inner query that orders the rows so the the row you want from each group is first, you simply add a group by on the relevant column.

Order inside Group By in Mysql statement

2 Answers2