Home > DeveloperSection > Forums > Select cell from same column with MAX function in mySql
Chintoo Semi

Total Post:135

Points:947
Posted on    July-17-2013 3:27 AM

 MSSQL Server MSSQL Server 
Ratings:


 1 Reply(s)
 731  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



shreesh chandra shukla
shreesh chandra shukla

Total Post:105

Points:735
Posted on    July-17-2013 8:20 AM

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.


Don't want to miss updates? Please click the below button!

Follow MindStick