Removing rows , if they have duplicate

Question

Lets suppose I have a following table in sql server 2008.

Column1   Column2   Column3
  1          2         1
  1          2         1
  5          1         1
  3          2         1
  3          2         1
  4          1         1

The output should be following

Column1   Column2   Column3
  5          1          1
  4          1          1

If a row has a duplicates present then , the original row and all the duplicate rows are to be deleted.If a row doesnot have any duplicates. Then it is kept. How can i achieve this?

Possible duplicate of [How to delete duplicate rows in sql server?](http://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server) — TT., Feb 22 '16 at 12:26
Possible dublicate of: http://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server — XsiSecOfficial, Feb 22 '16 at 12:26

score 1 · Accepted Answer · answered Feb 22 '16 at 12:55

Try like this,

DECLARE @Table TABLE (
    Column1 INT
    ,Column2 INT
    ,Column3 INT
    )


INSERT INTO @Table
(Column1,Column2,Column3)
VALUES
(1,2,1),
(1,2,1),
(5,1,1),
(3,2,1),
(3,2,1),
(4,1,1)

SELECT *
FROM @Table

DELETE t1
FROM @Table t1
INNER JOIN (
    SELECT Column1
        ,Column2
        ,Column3
    FROM @Table t
    GROUP BY Column1
        ,Column2
        ,Column3
    HAVING count(*) > 1
    ) t2 ON t1.column1 = t2.Column1
    AND t1.column2 = t2.column2
    AND t1.column3 = t2.column3

SELECT * FROM @Table

Removing rows , if they have duplicate

1 Answers1