Delete duplicate rows keeping the first row

Garima Bansal

How can I delete duplicate rows where no unique row id exists?

My table is

col1  col2 col3 col4 col5 col6 col7
John  1    1    1    1    1    1 
John  1    1    1    1    1    1
Sally 2    2    2    2    2    2
Sally 2    2    2    2    2    2

I want to be left with the following after the duplicate removal:

john  1    1    1    1    1    1
sally 2    2    2    2    2    2

I've tried a few queries but I think they depend on having a row id as I don't get the desired result.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Vishal Joshi2y ago

Accepted answer

Hello

Please check the below SQL query to delete the duplicate rows.

WITH CTE([Col1], 
    [Col2], 
    [Col3], 
    DuplicateCount)
AS (SELECT [Col1], 
           [Col2], 
           [Col3], 
           ROW_NUMBER() OVER(PARTITION BY [Col1], 
                                          [Col2], 
                                          [Col3]
           ORDER BY Col1) AS DuplicateCount
    FROM [SampleDB].[dbo].[TableName])
DELETE FROM CTE WHERE DuplicateCount > 1;

Thanks