MSSQL Query to delete duplicate rows from a TABLE leaving distinct rows in table

Question

I need to delete all duplicate rows except the first occurrence of similar rows from a Table Log having the same customer_id and deactivation_date. How do I achieve that with minimum number of sql statements.

I Am using MS SQL Server 2008 express edition.

Answer 1

Something like:

DELETE FROM Log WHERE LogId NOT IN 
 (SELECT Min(LogId) FROM Log GROUP BY customer_id, deactivation_date)

Answer 2

Or:

with cte as (
    select row_number() over (partition by customer_id, deactivation_date
    order by log_id) as rn
    from Log)
delete from cte
    where rn > 1;