CODEWITHSUNDEEP
sql-server
Venkat
Venkat

Reputation: 2156

Delete records from table by keeping the first 100 records

I want to keep the latest 100 records (lastmodifiedtime is a datetime column)and delete the remaining records. I am struggling to get the query for the purpose.

 ;WITH CTE AS
 (
 SELECT TOP 100 *
 FROM [mytable]
 ORDER BY LastModifiedTime desc
 )
  DELETE FROM CTE

Upvotes: 0

Views: 2467

Answers (4)

SQL Taylor
SQL Taylor

Reputation: 138

Nobody has used offset yet, so I thought I'd put in my two cents!

; WITH cte_Offset (
SELECT myTableID
  FROM [myTable]
 ORDER BY LastModifiedTime Desc
OFFSET 100 ROWS )

DELETE 
  FROM myTable 
 WHERE myTableID IN (SELECT myTableID FROM cte_Offset)

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135809

Sticking with the CTE idea, you could leverage the ROW_NUMBER function:

WITH cte AS (
    SELECT ROW_NUMBER() OVER(ORDER BY LastModifiedTime DESC) AS RowNum
        FROM [mytable]
)
DELETE FROM cte
    WHERE RowNum > 100;

Upvotes: 1

benjamin moskovits
benjamin moskovits

Reputation: 5458

--assuming ean is a unique key column in x1

with o1 as (select Top 100 ean from x1 order by DATE1)
 delete from x1 where ean not in (select ean from o1)

Upvotes: 0

Tanner
Tanner

Reputation: 22733

A query like this will give you the id's of the latest 100 records:

SELECT TOP 100 ID
FROM [YOUR_TABLE]
ORDER BY lastmodifiedtime DESC

So all you have to do is excluded these id's from your delete:

DELETE FROM [YOUR_TABLE]
WHERE ID NOT IN 
      (SELECT TOP 100 Id
       FROM [YOUR_TABLE]
       ORDER BY lastmodifiedtime DESC)

Just replace ID with your identifying field and [YOUR_TABLE] with your table name.

Upvotes: 4

Related Questions