CODEWITHSUNDEEP
sqlsql-servert-sqldatetimesql-delete
סטנלי גרונן
סטנלי גרונן

Reputation: 2997

Delete all rows except 100 most recent ones

I am using MS SQL Server and I have a table named Logs that looks like this:

table logs(dateTime, errorDescription)

Records are added frequently so the table becomes quite big after few days/weeks.

I need to perform a little cleanup periodically: I need query that would delete older rows and keep only the most recent 100 rows in the table.

I understand it would have been better to have it delete records older than some date... but I am asked to do it as described above.

Upvotes: 12

Views: 13886

Answers (5)

Salman Arshad
Salman Arshad

Reputation: 272106

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
)

Upvotes: 21

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Instead of using NOT EXISTS, just use >=:

WITH keepers AS (
    SELECT TOP 100 [DateTime]
    FROM dbo.Logs
    ORDER BY [DateTime] DESC
   )
DELETE FROM dbo.Logs a
    WHERE l.DateTime < (SELECT MIN([DateTime]) FROM keepers);

I'm not sure if there are lock settings where new rows could be added in while the delete is running. If so, this would still be safe for that.

You can actually simplify this in SQL Server 2012+:

DELETE FROM dbo.Logs a
    WHERE l.DateTime < (SELECT [DateTime] 
                        FROM dbo.logs
                        ORDER BY [DateTime]
                        OFFSET 99 FETCH FIRST 1 ROW ONLY
                       );

Upvotes: 3

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

This works for me:

;with cte as(select top(select count(*) - 100 from table) * from table order by dt)
delete from cte

Upvotes: 1

Fruitbat
Fruitbat

Reputation: 774

DECLARE @cutoff DATETIME
SELECT TOP 100 @cutoff = [DateTime] FROM Logs ORDER BY [DateTime] DESC
DELETE FROM Logs WHERE [DateTime] < @cutoff

Upvotes: 1

Matt
Matt

Reputation: 1155

While I agree with others that this is probably not the way to go, here's a way to do it anyway:

;WITH keepers AS
(   SELECT TOP 100 [DateTime]
    FROM dbo.Logs
    ORDER BY [DateTime] DESC )
DELETE FROM dbo.Logs a
WHERE NOT EXISTS ( SELECT 1 FROM keepers b WHERE b.[DateTime] = a.[DateTime] )

Upvotes: 4

Related Questions