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



Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading