在mysql 5.7中选择具有最大日期的不同行

问题描述：

假设有查询

SELECT c_id, id, max(date) as max_date FROM table
GROUP BY c_id,updated

结果如下:

c_id, id, max_date
1     5   2017-12-28 16:09:20 
1     6   2019-12-28 16:09:20
2     7   2017-12-28 16:09:20
2     8   2019-12-28 16:09:20

我希望得到:

c_id, id, max_date 
1     6   2019-12-28 16:09:20
2     8   2019-12-28 16:09:20

如何在 mysql 5.7 中实现?

How to achieve that in mysql 5.7?

答

使用相关子查询:

select t.*
from t
where t.date = (select max(t2.date) from t t2 where t2.c_id = t.c_id);