How To Delete The Last X Records From A Table By Group



TODO:

Have you ever wanted to keep the last X records for a Customer, Employee, Order, etc?

 

SOLUTION:

DELETE c FROM (
  SELECT *, rn=row_number() OVER (PARTITION BY CustomerId ORDER BY Id desc)
  FROM CustomerHistory
) c
WHERE rn > 3

 

NOTES:

The SQL above will keep the last 3 records in the table CustomerHistory.  The Grouping is by CustomerId, and Id is an Identity column, which ensures we keep the last 3 records (order by desc).  If you want to test it, just execute the statement as a select (Change DELETE c to SELECT CustomerId, Id, rn

Delete Duplicate Records From A Table



TODO:

Have you ever wanted to remove duplicate entries from a table using T-SQL?

 

SOLUTION:

 

BEGIN TRANSACTION

Delete from MyTable where row_id <
(Select Max(row_id) from MyTable mt where MyTable.Keywords = mt.Keywords)

--Verify data, then un-comment the commit
--COMMIT

 

 

NOTES:

This example deletes rows with duplicate 'Keywords' data.  'Keywords' is my column name.