Delete all but top n from database table in SQL
Asked Answered
T

11

98

What's the best way to delete all rows from a table in sql but to keep n number of rows on the top?

Transcendentalism answered 5/9, 2008 at 17:46 Comment(0)
G
94
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.

Glazing answered 5/9, 2008 at 17:47 Comment(6)
Just a note that you can solve the subquery performance issue via either creating a temporary table manually (assuming this is an infrequent operation) or writing the query as DELETE FROM Table WHERE ID NOT IN (SELECT id FROM (SELECT TOP 10 ID FROM Table) AS x) to force MySQL to create a temporary table.Hyperaemia
The subquery run multiple times, is it true ? #18791296Courtnay
@ Daniel Schaffer Doesn't sound like they got any db or business logic issue. Sounds like a totally normal retention policy.Ecklund
What if I don't have primary key in my table?Gizmo
not work in postgres (9.5.x)Sphygmograph
@Sphygmograph this is MS SQL Server only.Edition
G
33

I would select ID column(s) the set of rows that you want to keep into a temp table or table variable. Then delete all the rows that do not exist in the temp table. The syntax mentioned by another user:

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

Has a potential problem. The "SELECT TOP 10" query will be executed for each row in the table, which could be a huge performance hit. You want to avoid making the same query over and over again.

This syntax should work, based what you listed as your original SQL statement:

create table #nuke(NukeID int)

insert into #nuke(Nuke) select top 1000 id from article

delete article where not exists (select 1 from nuke where Nukeid = id)

drop table #nuke
Grigg answered 5/9, 2008 at 17:52 Comment(1)
insert into #nuke(Nuke) ... should probably be: insert into #nuke(NukeID) ... Also the name nuke is confusing because you are trying to NOT delete these rows. nuke is probably named after the fact that it will be deleted.Vegetal
H
15

Future reference for those of use who don't use MS SQL.

In PostgreSQL use ORDER BY and LIMIT instead of TOP.

DELETE FROM table
WHERE id NOT IN (SELECT id FROM table ORDER BY id LIMIT n);

MySQL -- well...

Error -- This version of MySQL does not yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Not yet I guess.

Hoban answered 5/9, 2008 at 18:39 Comment(0)
G
11

Here is how I did it. This method is faster and simpler:

Delete all but top n from database table in MS SQL using OFFSET command

WITH CTE AS
    (
    SELECT  ID
    FROM    dbo.TableName
    ORDER BY ID DESC
    OFFSET 11 ROWS
    )
DELETE CTE;

Replace ID with column by which you want to sort. Replace number after OFFSET with number of rows which you want to keep. Choose DESC or ASC - whatever suits your case.

Gramarye answered 24/4, 2019 at 16:32 Comment(2)
Wouldn't offset be the number of rows you want to keep in this case?Goldshlag
@Goldshlag Yes.Gramarye
D
6

I think using a virtual table would be much better than an IN-clause or temp table.

DELETE 
    Product
FROM
    Product
    LEFT OUTER JOIN
    (
        SELECT TOP 10
            Product.id
        FROM
            Product
    ) TopProducts ON Product.id = TopProducts.id
WHERE
    TopProducts.id IS NULL
Duchess answered 5/11, 2012 at 5:18 Comment(0)
R
4

This really is going to be language specific, but I would likely use something like the following for SQL server.

declare @n int
SET @n = SELECT Count(*) FROM dTABLE;
DELETE TOP (@n - 10 ) FROM dTable

if you don't care about the exact number of rows, there is always

DELETE TOP 90 PERCENT FROM dTABLE;
Raindrop answered 3/2, 2009 at 22:15 Comment(3)
Neither of these work. The question asks how to keep only the top N rows in a table. Both of these examples keep only the bottom N rows.Ganda
Works fine in MSSQL. Just add a sorting to delete the bottom instead of the top?Ragged
Sure if you can do an orderby... my table is hugeBank
S
2

I don't know about other flavors but MySQL DELETE allows LIMIT.

If you could order things so that the n rows you want to keep are at the bottom, then you could do a DELETE FROM table LIMIT tablecount-n.

Edit

Oooo. I think I like Cory Foy's answer better, assuming it works in your case. My way feels a little clunky by comparison.

Siple answered 5/9, 2008 at 17:48 Comment(0)
T
1

I've got a trick to avoid executing the TOP expression for every row. We can combine TOP with MAX to get the MaxId we want to keep. Then we just delete everything greater than MaxId.

-- Declare Variable to hold the highest id we want to keep. 
DECLARE @MaxId as int = (
SELECT MAX(temp.ID)
FROM (SELECT TOP 10 ID FROM table ORDER BY ID ASC) temp
)

-- Delete anything greater than MaxId. If MaxId is null, there is nothing to delete.
IF @MaxId IS NOT NULL
    DELETE FROM table WHERE ID > @MaxId

Note: It is important to use ORDER BY when declaring MaxId to ensure proper results are queried.

Tonkin answered 17/8, 2020 at 17:17 Comment(0)
T
0

I would solve it using the technique below. The example expect an article table with an id on each row.

Delete article where id not in (select top 1000 id from article)

Edit: Too slow to answer my own question ...

Transcendentalism answered 5/9, 2008 at 17:49 Comment(0)
P
0

Refactored?

Delete a From Table a Inner Join (
    Select Top (Select Count(tableID) From Table) - 10) 
        From Table Order By tableID Desc
) b On b.tableID = A.tableID

edit: tried them both in the query analyzer, current answer is fasted (damn order by...)

Platto answered 5/9, 2008 at 18:35 Comment(0)
B
0

Better way would be to insert the rows you DO want into another table, drop the original table and then rename the new table so it has the same name as the old table

Bencion answered 5/9, 2008 at 18:37 Comment(1)
Why is that better? Faster? Requires a couple of extra commands to accomplish.Ragged

© 2022 - 2024 — McMap. All rights reserved.