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.

Does this "log" table have an id of it's own, that's unique to each row? — Bridge, Apr 27 '12 at 07:25
Hey Akhil, you want to keep only one unique record? or the first One record? — Shaikh Farooque, Apr 27 '12 at 07:25
see this :-- http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql — Javascript Coder, Apr 27 '12 at 07:26

score 16 · Accepted Answer · answered Apr 27 '12 at 07:29

16

Something like:

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

answered Apr 27 '12 at 07:29

Fedor Hajdu

4,657
3
32
50

score 14 · Answer 2 · answered Apr 27 '12 at 07:32

14

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;

answered Apr 27 '12 at 07:32

MicSim

26,265
16
90
133

score 2 · Answer 3 · answered Apr 27 '12 at 07:28

You are not giving us much to work on. But maybe something like this:

;WITH CTE
AS
(
    SELECT
        ROW_NUMBER() OVER(PARTITION BY [Log].customer_id ORDER BY 
                              deactivation_date) AS RowNbr,
        [Log].*
    FROM
        [Log]
)
DELETE FROM [Log]
WHERE EXISTS
    (
        SELECT
            NULL
        FROM
            CTE
        WHERE
            CTE.RowNbr>1
            AND CTE.log_id =[Log].log_id 
    )

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

3 Answers3