SELECT CELL FROM SAME COLUMN WITH MAX FUNCTION IN MYSQL

Chintoo Semi

Total Post:135

Points:947
Posted by  Chintoo Semi
 1018  View(s)
Ratings:
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. shreesh chandra shukla

    Post:105

    Points:735
    Re: Select cell from same column with MAX function in mySql

    Solution!

    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

    INNER JOIN

    (

        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.

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!