HowTo DELETE using TOP and ORDER BY
From Frederick Chapleau Wiki
Contents |
Overview
There is many ways of deleting from a table... but when you must add where and order by conditions, it is not simple as it seems.
A complete case is here, to simplify tests of this kind of SQL.
Test & Code
Test Table Creation
CREATE TABLE [dbo].[#tmpTest] ( [TestId] uniqueidentifier NOT NULL, [Field1] nvarchar(50) NULL, [Field2] nvarchar(50) NOT NULL, [Field3] nvarchar(50) NOT NULL, [LogDate] datetime NOT NULL, CONSTRAINT [PK_tmpTest] PRIMARY KEY CLUSTERED ([TestId] ASC) )
Data
INSERT #tmpTest(TestId, Field1, Field2, Field3, LogDate) VALUES (NEWID(), 'Test 11', 'Test 12', 'Test 13', DateAdd(N, 1, GETDATE())) INSERT #tmpTest(TestId, Field1, Field2, Field3, LogDate) VALUES (NEWID(), 'Test 21', 'Test 22', 'Test 23', DateAdd(N, 2, GETDATE())) INSERT #tmpTest(TestId, Field1, Field2, Field3, LogDate) VALUES (NEWID(), 'Test 31', 'Test 32', 'Test 33', DateAdd(N, 3, GETDATE())) INSERT #tmpTest(TestId, Field1, Field2, Field3, LogDate) VALUES (NEWID(), 'Test 41', 'Test 42', 'Test 43', DateAdd(N, 4, GETDATE())) INSERT #tmpTest(TestId, Field1, Field2, Field3, LogDate) VALUES (NEWID(), 'Test 51', 'Test 52', 'Test 53', DateAdd(N, 5, GETDATE())) INSERT #tmpTest(TestId, Field1, Field2, Field3, LogDate) VALUES (NEWID(), 'Test 61', 'Test 62', 'Test 63', DateAdd(N, 6, GETDATE())) INSERT #tmpTest(TestId, Field1, Field2, Field3, LogDate) VALUES (NEWID(), 'Test 71', 'Test 72', 'Test 73', DateAdd(N, 7, GETDATE())) INSERT #tmpTest(TestId, Field1, Field2, Field3, LogDate) VALUES (NEWID(), 'Test 81', 'Test 82', 'Test 83', DateAdd(N, 8, GETDATE())) INSERT #tmpTest(TestId, Field1, Field2, Field3, LogDate) VALUES (NEWID(), 'Test 91', 'Test 92', 'Test 93', DateAdd(N, 9, GETDATE()))
Tests
SELECT * FROM #tmpTest DELETE t FROM #tmpTest t INNER JOIN (SELECT TOP(5) * FROM #tmpTest ORDER BY LogDate DESC) s ON t.LogDate = s.LogDate SELECT * FROM #tmpTest
Cleanup
DROP TABLE #tmpTest
