Delete All But Top N from Database Table in SQL

Delete all but top n from database table in SQL

DELETE FROM Table WHERE ID NOT IN (SELECT TOP 10 ID FROM Table)

Edit:

Chris brings up a good performance hit since the TOP 10 query would be run for each row. If this is a one time thing, then it may not be as big of a deal, but if it is a common thing, then I did look closer at it.

how to remove all but top 10 records from database sql server 2012?

I have a suggestion of Deleting 90% data from your database by the using the following command, to keep 10 records you need a bit more table specific query, but if you want to delete 90% data from all the tables in your database you can use the following system stored procedure.

EXECUTE sp_MSforeachtable 'DELETE TOP (90) PERCENT FROM  ?'

Delete all but top n from database table in SQLite for java/android

You can try

db.execSQL("Delete From "+TABLE_BOOKMARKS+"  where "+ KEY_ID  + " not in (Select "+ KEY_ID +" from "+ TABLE_BOOKMARKS + " order by " + KEY_ID +" limit 10)");

Delete all rows except first N from a table having single column

If you can order your records by friends_name, and if there are no duplicates, you could use this:

DELETE FROM names
WHERE
friends_name NOT IN (
SELECT * FROM (
SELECT friends_name
FROM names
ORDER BY friends_name
LIMIT 10) s
)

Please see fiddle here.

Or you can use this:

DELETE FROM names ORDER BY friends_name DESC
LIMIT total_records-10

where total_records is (SELECT COUNT(*) FROM names), but you have to do this by code, you can't put a count in the LIMIT clause of your query.

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;

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 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.

SQL Delete all results except Top and Bottom

You could use a deletable CTE, something like:

WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY CAST(ts_col AS date) ORDER BY ts_col) rn_start,
ROW_NUMBER() OVER (PARTITION BY CAST(ts_col AS date) ORDER BY ts_col DESC) rn_end
FROM yourTable
)

DELETE
FROM cte
WHERE rn_start <> 1 AND rn_end <> 1;

Here, I assume that you have some timestamp column called ts_col. The above logic partitions row number based on the date itself (not the timestamp), then identifies the first and last records for each date, sparing those records from deletion.



Related Topics



Leave a reply



Submit