How to delete duplicate values by using CTE expressions.?

Delete duplicate values using CTE. 

CTE, is used for some complex queries or operations.Here is the example for deleting the Duplicates values. We have a sample table (EMP) for the example.

Select * from EMP

EID	ENAME	DEPT
1	Sunil	IT
2	Deepak	IT
3	Prateek	HR
4	Surendra	HR
5	sunil	IT

For removing the duplicate employee, i.e. ‘Sunil’ from the table, we create a CTE:

;WITH EliminateDup(Eid,Name,Dept,RowID) AS

(

SELECT Eid,Ename,Dept, ROW_NUMBER()OVER(PARTITION BY Ename,Dept ORDER BY EID)AS RowID

FROM EMP

)

DELETE FROM EliminateDup WHERE RID>1

The query below creates a temporary resultset as :

SELECT Eid,Ename,Dept, ROW_NUMBER()OVER(PARTITION BY Ename,Dept ORDER BY EID)AS RowID FROM EMP

EID	ENAME	DEPT	RowID
2	Deepak	IT	1
1	Suni	IT	1
5	sunil	IT	2
4	Surendra	HR	1
3	Prateek	HR	1

And later, remove the duplicate values with the DELETE