SQL Query: Delete All Records from the Table Except Latest N

SQL query: Delete all records from the table except latest N?

You cannot delete the records that way, the main issue being that you cannot use a subquery to specify the value of a LIMIT clause.

This works (tested in MySQL 5.0.67):

DELETE FROM `table`
WHERE id NOT IN (
  SELECT id
  FROM (
    SELECT id
    FROM `table`
    ORDER BY id DESC
    LIMIT 42 -- keep this many records
  ) foo
);

The intermediate subquery is required. Without it we'd run into two errors:

1. SQL Error (1093): You can't specify target table 'table' for update in FROM clause - MySQL doesn't allow you to refer to the table you are deleting from within a direct subquery.
2. SQL Error (1235): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' - You can't use the LIMIT clause within a direct subquery of a NOT IN operator.

Fortunately, using an intermediate subquery allows us to bypass both of these limitations.

---

Nicole has pointed out this query can be optimised significantly for certain use cases (such as this one). I recommend reading that answer as well to see if it fits yours.

MySQL - Efficiently delete all records except last N

Try DELETE JOIN:

delete a from access a left join (
    select id
    from access
    order by id desc limit 1000
) b on a.id = b.id
where b.id is null;

If you want to keep top 1000 records of a given user (say 123) from deleting :

delete a from access a left join (
    select id
    from access
    where userid = 123
    order by id desc limit 1000
) b on a.id = b.id
where b.id is null;

If you want to delete rows only for user 123 except the top 1000 for that user:

delete a from access a left join (
    select id
    from access
    where userid = 123
    order by id desc limit 1000
) b on a.id = b.id
where b.id is null
and a.userid = 123;

(SQL) Delete all rows except the newest one

If you want to delete all the rows the SQL is :

Delete  from theTable;

If you want to exclude the newest, hoping that you have an id you write like this:

Delete  from theTable where id <> (Select max (id) from theTable)

Delete all records from a table except latest N months records (sorted by id)

Try this:

DELETE
    FROM Tablex 
    INNER JOIN (SELECT max(InsertDt ) LastDate, id FROM Tablex  GROUP by id) LastDates ON LastDates.id = Tablex.id
    WHERE InsertDt < dateadd(m,-3,LastDates.LastDate)

Delete all rows except 100 most recent ones

You can use one of the following:

-- offset clause
WITH goners AS (
    SELECT *
    FROM Logs
    ORDER BY DateTime DESC
    OFFSET 100 ROWS 
)
DELETE FROM goners

-- numbered rows
WITH goners AS (
    SELECT ROW_NUMBER() OVER(ORDER BY DateTime DESC) AS rn, Logs.*
    FROM Logs
)
DELETE FROM goners
WHERE rn > 100

-- nth value
-- note that this "keeps" all rows that tie for last place
DELETE FROM Logs
WHERE DateTime < (
    SELECT MIN(DateTime)
    FROM (
        SELECT TOP 100 DateTime
        FROM Logs
        ORDER BY DateTime DESC
    ) AS x
)

SQL - remove all except the last N rows by group

You could use a subquery here:

DELETE
FROM messages m1
WHERE id NOT IN (SELECT m2.id
                 FROM messages m2
                 WHERE m2.client_id = m1.client_id
                 ORDER BY m2.id DESC
                 LIMIT 5);

The above logic would prune away all client records which are not the most recent five. Should a given client have fewer than 5 records, then no deletions at all would happen.

Delete all rows, except last 10 for each client that has related row(s) in the table in one query?

You can do it like this:

DELETE FROM `recently_viewed`
WHERE `recently_viewed`.`id` NOT IN (
        SELECT id 
          FROM ( 
            SELECT t.`id`,count(*) as rnk
            FROM `recently_viewed` t
            INNER JOIN `recently_viewed` s
            ON(t.`client_id` = s.`client_id` and t.added <= s.added)
            WHERE t.`client_id` IN (SELECT `id` FROM `klijenti`)
            GROUP BY t.`ID`
         ) x
        WHERE rnk <= 5
      )
AND `client_id` <> 0

---

Related Topics