Write a SQL query to find duplicate employee IDs in the employee table, keep one record, and delete the remaining duplicates.

Use a CTE with ROW_NUMBER to identify duplicates and then delete the extra rows. Example:

WITH cte AS (

SELECT *, ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY employee_id) AS rn

FROM employee

)

DELETE FROM cte

WHERE rn > 1;

This keeps the first row for each emp_id and removes the rest. If you need to keep a specific row (e.g., the most recent hire_date), adjust the ORDER BY clause accordingly. Always back up the table before running deletes and consider using a staging table if the dataset is large. The operation runs in O(n log n) time due to the sorting required for ROW_NUMBER.