SQL Server Delete all rows except 100 most recent ones

7cwmlq89 于 2024-01-05 发布在其他

关注(0)|答案(5)|浏览(105)

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

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.

sql-server

来源：https://stackoverflow.com/questions/31253628/delete-all-rows-except-100-most-recent-ones

5条答案

按热度按时间

rbpvctlc1#

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
)

赞(0）回复(0）举报 2024-01-05

kg7wmglp2#

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] )

赞(0）回复(0）举报 2024-01-05

axkjgtzd3#

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
                       );

赞(0）回复(0）举报 2024-01-05

kyxcudwk4#

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

赞(0）回复(0）举报 2024-01-05

vqlkdk9b5#

This works for me:

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

赞(0）回复(0）举报 2024-01-05

我来回答

SQL Server Delete all rows except 100 most recent ones

5条答案

相关问题

热门标签

最新问答