SQL: How to Delete Latest Records in MSSQL?

on
Question:
How to delete nth number latest records in Microsoft SQL Server (MSSQL) without having any subquery in/within DELETE statement?

Answer:
Assume that we have below table. And we want to delete the latest 3 rows that Name column contains Liana ordered by LastUpdated column in descending order.

TABLE MyTable (Id INT, Code NVARCHAR(10), Name NVARCHAR(100), LastUpdated DATETIME)

What we usually do or common query.



DELETE FROM MyTable WHERE Id IN
(SELECT TOP 3 Id FROM MyTable WHERE Name LIKE '%Liana%' ORDER BY LastUpdated DESC);





What you can do by removing subquery.






WITH LatestNthTransaction AS
(DELETE TOP 3 * FROM MyTable WHERE Name LIKE '%Liana%' ORDER BY LastUpdated DESC)
DELETE FROM LatestNthTransaction;