mysql - How to keep the latest record of a table

Question

I have a table with following structure

Date	train
time1	train1
time2	train2
time3	train1
time4	train2

question from:https://stackoverflow.com/questions/65866994/how-to-keep-the-latest-record-of-a-table

Answer 1

One method is for selecting the most recent rows is:

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

If you actually want to modify the table and delete the older rows;

delete t
    from releng_retry_test_phases t join
         (select t2.train, max(date) as max_date
          from releng_retry_test_phases t2
          group by t2.train
         ) t2
         using (train)
    where t.date < t2.max_date;