SQL - Remove duplicated records

-- Remove duplicated records

DECLARE @DupDemo TABLE

(

   A VARCHAR(15),

   B VARCHAR(15),

   C VARCHAR(15)

)

-- Insert testing records

INSERT INTO @DupDemo VALUES

('1','a','2'),

('1','a','1'),

('2','c','6'),

('2','c','3'),

('1','a','1'),

('3','b','3')

-- Original Records

SELECT *

FROM @DupDemo

-- Show the duplicated records, only to check column A and B.

SELECT ROW_NUMBER() OVER(PARTITION BY A,

                                      B

                    ORDER BY C) AS ID,

             A,

             B,

             C

      FROM @DupDemo

-- Delete the duplicated records

DELETE D

FROM(

      SELECT ROW_NUMBER() OVER(PARTITION BY A,

                                            B

                               ORDER BY C) AS ID,

             A,

             B,

             C

      FROM @DupDemo

)D

WHERE D.ID > 1

-- After delete operation

SELECT *

FROM @DupDemo

-- Show DEMO 2

DELETE FROM

@DupDemo

-- Insert test records

INSERT INTO @DupDemo VALUES

('1','a','2'),

('1','a','1'),

('2','c','6'),

('2','c','3'),

('1','a','1'),

('3','b','3');

-- With TEMP table

WITH Dup AS

(

   SELECT ROW_NUMBER() OVER(PARTITION BY A,

                                            B

                               ORDER BY C) AS ID,

             A,

             B,

             C

      FROM @DupDemo

)

DELETE FROM

Dup

WHERE ID > 1;

-- Show records after delete operation.

SELECT *

FROM @DupDemo