Delete all rows except 100 most recent ones

Question

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

Records are added here one after another so the table becomes quite big after few days/weeks.

What I need to do periodically is a little bit of cleanup. I.e. I need an SQL query that would delete older rows and keep only the most recent 100 records in this table.

I agree it would be better to have it "delete records older than some_date"... It was like this before, but the client wants it different :( So... here we are.

BTW: I'm a little bit disappointed about people throwing negatives on this question. Is there something wrong with it or what?... Imagine: this question produced already 4 answers!!! ... and one guy decided to throw negative on that! Now I really don't know what to think... Strange people around here :(

Answer 1

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

Answer 2

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
)