To delete duplicate records

 ✅ Using ROW_NUMBER() (Recommended)

WITH CTE AS (
    SELECT 
        EmployeeID, 
        Salary, 
        Department, 
        ROW_NUMBER() OVER (PARTITION BY Salary, Department ORDER BY EmployeeID) AS RowNum
    FROM Employees
)
DELETE FROM Employees
WHERE EmployeeID IN (
    SELECT EmployeeID FROM CTE WHERE RowNum > 1
);

🔹 Explanation:

The ROW_NUMBER() function assigns a unique number to each duplicate row within the same (Salary, Department).
PARTITION BY Salary, Department ensures numbering resets for each Salary & Department combination.
ORDER BY EmployeeID determines which row to keep (lowest EmployeeID remains).
The DELETE statement removes all rows where RowNum > 1, keeping only one record per (Salary, Department).
✅ Using DELETE with JOIN (Alternative)
DELETE E1
FROM Employees E1
JOIN Employees E2 
ON E1.Salary = E2.Salary 
AND E1.Department = E2.Department
AND E1.EmployeeID > E2.EmployeeID;
Explanation:

The self-join finds duplicate (Salary, Department).
It deletes the record where EmployeeID is greater, keeping the lowest EmployeeID.
SELECT EmployeeID, Salary, Department,
       ROW_NUMBER() OVER (PARTITION BY Salary, Department ORDER BY EmployeeID) AS RowNum
FROM Employees;