I have a two tables 1. main_table 2. log_table
On main_table a trigger is defined that copies the affected rows to log_table on delete and update. In log_table the columns are same except two extra column pid(primary key int) and updateat(time stamp).
Every month the cron job is deleting all the records from log_table that are more than 10 days old. which I need to change to delete all records except latest 10 for each row.
I did it via linq in which I am selecting distinct ids, and then I am looping over all the ids and deleting all data for that id except top 10.
This is working except it is painfully slow and unrealistic in production environment. I also tried to move this logic to Stored procedure with cursor but it is still slow.
I am not sure but I think there can other methods for achieving it, where I don't have to loop over?
The server is mssql 2012 if that matters.
Is this what you want?
with todelete as ( select l.*, row_number() over (partition by id order by updatedate desc) as seqnum from log_table l ) delete todelete where seqnum > 10;