PhuocLe - Microsoft Dynamics CRM

Delete Duplicate Rows Nov/01/2013


I have a query return a duplicate rows (duplicate by column: ColA and ColB).

And I want delete these duplicate rows (but keep the first row).

Now, I add a new column: DuplicateCount

Then I delete all rows by WHERE: DuplicateCount > 1. After that, I get the final results

SQL code snippet

IF OBJECT_ID('tempdb..#duplicate') IS NOT NULL DROP TABLE #duplicate
SELECT * 
	INTO #duplicate
FROM(
	SELECT 1 AS ColA, 1 AS ColB, 0 AS ColC
	UNION ALL
	SELECT 1, 1, 1 --duplicate
	UNION ALL
	SELECT 1, 1, 2 --duplicate
	UNION ALL
	SELECT 1, 2, 3
	UNION ALL
	SELECT 1, 2, 4 --duplicate
	UNION ALL
	SELECT 1, 3, 4
	UNION ALL
	SELECT 1, 4, 5
) AS duplicate
SELECT * FROM #duplicate
GO

;WITH CTE (ColA, ColB, ColC, DuplicateCount)
AS
(
	SELECT ColA, ColB, ColC,
		ROW_NUMBER() OVER(PARTITION BY ColA, ColB ORDER BY ColA ASC, ColB ASC) AS DuplicateCount
	FROM #duplicate
)
DELETE FROM CTE WHERE DuplicateCount > 1
GO

SELECT * FROM #duplicate

Category: SQL
Tags:

Enter your comment

Your Name (*)
Your Email (*)
Website (*)