Delete all rows except 100 most recent ones
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
);
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] )
This works for me:
;with cte as(select top(select count(*) - 100 from table) * from table order by dt)
delete from cte
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
)