Deleting Duplicate Records

CREATE TABLE #Employee(
Employee_ID [int] Null,
Employee_Name varchar(20) Null,
Employee_Dept varchar(20) Null 
) ;

Insert into #Employee VALUES (1, 'Mark Dunn', 'HR');
Insert into #Employee VALUES (1, 'Mark Dunn', 'HR');
Insert into #Employee VALUES (2, 'John Ciesel', 'Production');
Insert into #Employee VALUES (2, 'John Ciesel', 'Production');
Insert into #Employee VALUES (2, 'John Ciesel', 'Production');
Insert into #Employee VALUES (3, 'Anna Dolf', 'Manufacturing');


-- Selecting Distinct Records
With CTE_Employee
AS
(
SelectEmployee_ID, Employee_Name, Employee_Dept,
ROW_NUMBER()
OVER (PARTITION BY Employee_ID, Employee_Name, Employee_Dept
ORDER BY Employee_ID, Employee_Name, Employee_Dept) AS RowNumber
from#Employee
)

SELECTEmployee_ID, Employee_Name, Employee_Dept
FROMCTE_Employee
WHERERowNumber = 1;


-- Selecting Duplicate Records
With CTE_Employee
AS
(
SelectEmployee_ID, Employee_Name, Employee_Dept,
ROW_NUMBER()
OVER (PARTITION BY Employee_ID, Employee_Name, Employee_Dept
ORDER BY Employee_ID, Employee_Name, Employee_Dept) AS RowNumber
from#Employee
)

SELECTEmployee_ID, Employee_Name, Employee_Dept
FROMCTE_Employee
WHERERowNumber > 1;


-- Deleting Duplicate Records
With CTE_Employee
AS
(
SelectEmployee_ID, Employee_Name, Employee_Dept,
ROW_NUMBER()
OVER (PARTITION BY Employee_ID, Employee_Name, Employee_Dept
ORDER BY Employee_ID, Employee_Name, Employee_Dept) AS RowNumber
from#Employee
)

DELETE
FROMCTE_Employee
WHERERowNumber > 1;