Select distinct rows with max date in mysql 5.7

Question

Suppose having query

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

And following result:

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

I expect to get:

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

How to achieve that in mysql 5.7?

Why are you grouping by `updated`? That means you want different rows for each value of that column. — Barmar, Aug 06 '20 at 16:30
Without grouping by updated, it will place the wrong id in the resultset 1 5 2019-12-28 16:09:20 2 7 2019-12-28 16:09:20 — Akhmed, Aug 06 '20 at 16:34
It's getting the wrong id because this isn't the right way to get the ID with the max date. It has nothing to do with updated. — Barmar, Aug 06 '20 at 16:37

score 0 · Answer 1 · answered Aug 06 '20 at 16:26

0

Use a correlated subquery:

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

answered Aug 06 '20 at 16:26

Gordon Linoff

There is a mistake in your query near "from t t2", maybe you mean "from table t2". And it produces the wrong number of rows. I receive 2 rows for one c_id and wrong dates also – Akhmed Aug 06 '20 at 16:31
@Ahmed . . . `table` isn't a reasonable table name so I made up a placeholder, `t`. – Gordon Linoff Aug 06 '20 at 16:36
not sure it works in mysql – Akhmed Aug 06 '20 at 16:37
@Ahmed It should work. See the linked question for lots of other ways to do it. Replace `t` with the actual name of your table. – Barmar Aug 06 '20 at 16:38
sorry. I didn't read your query properly. I thought there is table in the first place. By the way, it is t. Your query is correct, but do not solve my problem. – Akhmed Aug 06 '20 at 16:39

1 Answers1