How to delete old rows with same id and different timestamp in MySQL?

Question

I have a mysql table, a simplified version below:

ID    Col1        Col2              CreatedDate

232   my data     some data        2017-10-15 8:50:20
232   my data     some data        2017-10-14 8:52:20
232   my data     some data        2017-10-13 8:53:20
232   my data     some data        2017-10-12 8:55:20
232   my data     some data        2017-10-11 8:54:20

IDis intand CreatedDateisdatetime

I want to save the last two lines and delete all the rest with the same identifier. I will get IDfrom old.ID in the trigger (e.g. where ID=old.ID).

Any help?

Answer 1

This code will delete everything except the last two entries of old.IDyour trigger id .

DELETE FROM `mytable`
WHERE ID = old.ID AND CreatedDate not IN ( -- Delete rows ID = old.ID  but not in the subquery
  SELECT CreatedDate
  FROM (
    SELECT CreatedDate -- This select the last 2 from old.ID
    FROM `mytable`   
    WHERE ID = old.ID
    ORDER BY CreatedDate DESC
    LIMIT 2 -- keep last two record
  ) foo
)

Answer 2

In My sql there is no way to use LIMIT in a subquery, so you need to create as shown below:

delete from table_name 
where 
CreatedDate not in
  (select CreatedDate from 
            (select * from 
                      table_name 
                        where id='232' order by CreatedDate desc limit 2) as t);

Answer 3

.

DELETE FROM `mytable` WHERE ID and CreatedDate NOT IN(
        SELECT CreatedDate
        FROM (
            SELECT max(CreatedDate) AS CreatedDate,
            max(ID) AS ID FROM mytable GROUP BY ID

            UNION 

            SELECT max(CreatedDate), max(ID) 
            FROM mytable 
            WHERE CreatedDate not in
            ( 
                SELECT max(CreatedDate) as CreatedDate
                FROM mytable GROUP BY ID
            ) GROUP BY ID
            ) foo
        )

Answer 4

,

delete from table Where CreatedDate not in ( SELECT CreatedDate FROM table order by CreatedDate Desc limit 2 )

,

delete from table WHERE CreatedDate not in ( SELECT * FROM ( SELECT CreatedDate FROM table order by CreatedDate Desc limit 2 ) as t);