Delete all rows except 100 most recent ones

Question

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.

Answer 1

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
)