>I have a data fix to apply based on child records, where two detail records have sometimes been added in sequence. When this is the case, I need to remove the most recent one.
>
>So, basically, I have a Client table. Then, I have an Invoice table. This is the typical example scenario. This does not represent the real tables and fields I am using.
>
>For each client, I need to get the Invoice records. If the last two records of the Invoice table have the same status, for example Completed, I need to remove the most recent one.
>
>What would be the easier approach to resolve that?
;with cteRows as (select *, row_number() over (partition by ClientID order by AddedDate DESC) as RowNumber
from Invoices)
delete from cteRows R where RowNumber = 1 and Status = 'Completed'
and exists (select * from cteRows where RowNumber = 2 and Status = 'Completed'
and R.ClientId = cteRows.ClientId)
If it's not broken, fix it until it is.
My Blog