CTE Delete Duplicates Example

CTE + ROW_NUMBER() to Delete Duplicates

Step 1: Original Table

Duplicate Email: ram@gmail.com

WITH CTE AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY Email ORDER BY ID) AS rn
    FROM Employees
)
SELECT * FROM CTE;

ID	Name	Email	rn
1	Ram	ram@gmail.com	1
3	Ram	ram@gmail.com	2
5	Ram	ram@gmail.com	3
2	Shyam	shyam@gmail.com	1
4	Mohan	mohan@gmail.com	1

Explanation:

WITH CTE AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY Email ORDER BY ID) AS rn
    FROM Employees
)
DELETE FROM CTE
WHERE rn > 1;

Duplicates removed successfully ✅