161 ) ) Delete Duplicate Records

 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

2) Delete Duplicate Records

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Question: Write a query to delete duplicate records from a table while keeping one unique instance.

Query:

SQL

WITH CTE_Duplicates AS (
    SELECT 
        EmployeeID,
        FirstName,
        LastName,
        ROW_NUMBER() OVER (
            PARTITION BY FirstName, LastName, Email 
            ORDER BY (SELECT NULL)
        ) AS RowNum
    FROM Employees
)
DELETE FROM CTE_Duplicates
WHERE RowNum > 1;