TSQLRemoveDuplicatesWithLeftOuterJoin.sql

/* LEFT OUTER JOIN to remove duplicates*/

-- Build experiment table
DECLARE @dup TABLE(
	ColumnOne VARCHAR(1),
	ColumnTwo INT
)

-- Insert experiment values
INSERT INTO @dup VALUES('A',1)
INSERT INTO @dup VALUES('A',2)
INSERT INTO @dup VALUES('B',3)
INSERT INTO @dup VALUES('C',4)
INSERT INTO @dup VALUES('D',5)
INSERT INTO @dup VALUES('E',6)
INSERT INTO @dup VALUES('E',7)
INSERT INTO @dup VALUES('E',8)
INSERT INTO @dup VALUES('E',9)

-- See the duplicates
SELECT *
FROM @dup

-- What should exist in the table
SELECT ColumnOne, ColumnTwo = MIN(ColumnTwo) 
FROM @dup 
GROUP BY ColumnOne

-- What should be removed	
SELECT *
FROM @dup A
	LEFT OUTER JOIN (SELECT ColumnOne, ColumnTwo = MIN(ColumnTwo) FROM @dup GROUP BY ColumnOne) B ON A.ColumnTwo = B.ColumnTwo
WHERE B.ColumnTwo IS NULL

-- Remove the duplicates
DELETE @dup
FROM @dup A
	LEFT OUTER JOIN (SELECT ColumnOne, ColumnTwo = MIN(ColumnTwo) FROM @dup GROUP BY ColumnOne) B ON A.ColumnTwo = B.ColumnTwo
WHERE B.ColumnTwo IS NULL

-- See the results
SELECT *
FROM @dup

/*

Note: numerous ways to remove duplicates exist (such as the CTE), but these methods, including the CTE, show fast and effective methods.

*/