T-Sql – Keeping Top n rows in a table

The title sounds like related to a logging operation, isn’t it? Well yes, it is.

In an existing logging operation, I needed to ensure that the number of log entries remained below a specified threshhold. And I came up with the following sql quickly:

DECLARE @numEntriesToKeep int;
DECLARE @minTimeToKeep datetime;

SET @numEntriesToKeep = 1000;

SELECT TOP 1 @minTimeToKeep = LogCreateDate
FROM (SELECT TOP (@numEntriesToKeep) LogCreateDate
FROM EventLog
ORDER By LogCreateDate DESC) a
ORDER BY LogCreateDate;

DELETE FROM EventLog
WHERE LogCreateDate < @minTimeToKeep;

Now, I needed to keep the most recent (indicated by LogCreateDate column) rows, deleting all others. In case your table does not have a DateTime column to sort upon, you can easily you the IDENTITY column itself for deciding the most recent entries (i.e. you can replace LogCreateDate by LogId in most circumstances above, assuming LogId to be the IDENTITY column).

As is typical in Sql, there are various ways you can approach this, including cursors, temporary tables, local table variables and the like. However, I was interested in using a single Sql statement to get the task done. And the above was what I came up with. So, here’s a dissection of the above sql (yes, there are 2 Sql statements instead of one above):

Suppose we need to keep only the most recent 1000 rows. We basically need to find the time of creation of the 1000th row so that we can delete all rows with times less than this (in the above sql, LogCreateDate is assumed to be the datetime on which a particular log entry was created).

We order the rows in descending order of LogCreateDate and pick the top 1000 rows from it (the ones we need to preserve). Then we further order these 1000 rows in ascending order of LogCreateDate. The first of these rows now gives us the minimum time of log creation that we need to preserve. Deleting all rows with LogCreateDate less than this time and we are done.

The above sql might leave some extra rows in the table if multiple rows happen to have their LogCreateDate equal to @minTimeToKeep. However, that should not be an issue in most cases.

Also, if your table has an IDENTITY column (lets say LogId), you can also replace LogCreateDate in the above sql with LogId and achieve the same effect (using LogId further guarantees that no more than @numEntriesToKeep are left). However, LogId might not be strictly in the order of creation dates of rows (especially if you pass LogCreateDate manually to the table, instead of using GETDATE or GETUTCDATE sql functions).