Table for example:
event_id| group_id | updated_at
1 | 1 |2012-01-02
2 | 1 |2012-01-01
3 | 2 |2012-01-01
4 | 2 |2012-01-02
5 | 3 |2012-01-01
I need select all event_id with max date, grouped by group_id. So right result will be:
event_id| group_id | updated_at
1 | 1 |2012-01-02
4 | 2 |2012-01-02
5 | 3 |2012-01-01
Can I select event_id, from the same row that MAX(updated_at) has been selected?
SELECT event_id, group_id, MAX(updated_at)
FROM my_table
GROUP BY group_id;
Thanks in advance
shreesh chandra shukla
17-Jul-2013MySQL may allow the type of syntax you suggest - specifying non-aggregate columns that are also not part of the group by - but it is non-standard and IMHO a bad habit to get into.
Instead, you would do:
SELECT t1.event_id, t1.group_id, t1.updated_at
FROM my_table t1
INNER JOIN
(
SELECT group_id, MAX(updated_at) AS maupdated_at
FROM my_table
GROUP BY group_id
) t2 ON t1.group_id = t2.group_id AND t1.updated_at = t2.maupdated_at
Note though if updated_at is non-unique for a given group_id, this query will return all of said rows.