How to keep the latest record of a table

user_5117474 0
Viewed 313

I have a table with following structure

Date train
time1 train1
time2 train2
time3 train1
time4 train2

I want to create a new table and keeping only the latest record of each distinct train

Date train
time3 train1
time4 train2

How should I achieve so?

mysql sql
3 Answers

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;
user_11440350

You can use ROW_NUMBER() to identify the rows you want:

select date, train
from (
  select *,
    row_number() over(partition by train order by date desc) as rn
) x
where rn = 1
user_64361910 
WITH temp As(
    SELECT *, Row_Number() over (PARTITION BY train ORDER BY date DESC ) as 
    rowNumber FROM table 
)
SELECT date, train FROM temp WHERE rowNumber = 1

You can use row_number() method.

user_149102380
Related