0

Suppose having data in table tbl:

c_id  id       date
251   215682   2016-12-23 11:57:57
251   218596   2017-03-28 14:08:59
251   230756   2019-12-28 16:09:20
254   254      2017-12-28 16:09:20
254   391849   2019-09-16 15:48:54
254   431331   2019-09-16 15:48:54

I expect to get:

c_id, id, date 
251   230756   2019-12-28 16:09:20
254   391849   2019-09-16 15:48:54

There is also one thing to notice. There can be rows with the duplicated dates for there same c_id. For example, in the case of c_id = 254. There is no difference which one to return or we can return the one with bigger id, but it is not the problem of this question.

Here is the SQL fiddle page http://sqlfiddle.com/#!9/e7bc37/1

How to achieve that in mysql 5.7?

Akhmed
  • 1,141
  • 4
  • 21
  • 49
  • You asked the same question: https://stackoverflow.com/questions/63287914/select-distinct-rows-with-max-date-in-mysql-5-7 which was closed as a duplicate. Didn't you find the solution in the duplicate link? – forpas Aug 06 '20 at 17:15

1 Answers1

0

Assuming that id is unique, you can use a correlated subquery with limit:

select t.*
from t
where t.id = (select t2.id
              from t t2
              where t2.c_id = t.c_id
              order by t2.date desc
              limit 1
             );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786