Get id of max value in group

Question

I have a table and i would like to gather the id of the items from each group with the max value on a column but i have a problem.

SELECT group_id, MAX(time) 
FROM mytable
GROUP BY group_id

This way i get the correct rows but i need the id:

SELECT id,group_id,MAX(time)
FROM mytable
GROUP BY id,group_id

This way i got all the rows. How could i achieve to get the ID of max value row for time from each group?

Sample Data

id = 1, group_id = 1, time = 2014.01.03
id = 2, group_id = 1, time = 2014.01.04
id = 3, group_id = 2, time = 2014.01.04
id = 4, group_id = 2, time = 2014.01.02
id = 5, group_id = 3, time = 2014.01.01

and from that i should get id: 2,3,5 Thanks!

Answer 1

Have a look at the below demo

DROP TABLE IF EXISTS mytable;

CREATE TABLE mytable(id INT , group_id INT , time_st DATE);

INSERT INTO mytable VALUES(1, 1, '2014-01-03'),(2, 1, '2014-01-04'),(3, 2, '2014-01-04'),(4, 2, '2014-01-02'),(5, 3, '2014-01-01');

/** Check all data **/
SELECT * FROM mytable;
+------+----------+------------+
| id   | group_id | time_st    |
+------+----------+------------+
|    1 |        1 | 2014-01-03 |
|    2 |        1 | 2014-01-04 |
|    3 |        2 | 2014-01-04 |
|    4 |        2 | 2014-01-02 |
|    5 |        3 | 2014-01-01 |
+------+----------+------------+


/** Query for Actual output**/

SELECT 
    id
FROM
    mytable 
JOIN
    ( 
      SELECT group_id, MAX(time_st) as max_time 
      FROM mytable GROUP BY group_id 
    ) max_time_table 
ON mytable.group_id = max_time_table.group_id AND mytable.time_st = max_time_table.max_time;    
+------+
| id   |
+------+
|    2 |
|    3 |
|    5 |
+------+