Select cell from same column with MAX function in mySql

Total Post:135


 1414  View(s)
Rate this:
HI developers!

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

  1. Re: Select cell from same column with MAX function in mySql


    MySQL 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



        SELECT group_id, MAX(updated_at) AS max_updated_at

        FROM my_table

        GROUP BY group_id

    ) t2 ON t1.group_id = t2.group_id AND t1.updated_at = t2.max_updated_at

    Note though if updated_at is non-unique for a given group_id, this query will return all of said rows.


Please check, If you want to make this post sponsored

You are not a Sponsored Member. Click Here to Subscribe the Membership.