Delete duplicate names from the table below. If a duplicate name is encountered then the query should keep the record that contains the most current ‘Date’.
DELETE e
FROM
employee e
INNER JOIN
(
SELECT e2.id,e2.name,e2.date, RANK()OVER(PARTITION BY e2.name ORDER BY e2.date DESC) AS rank2
FROM employee e2
)t
ON e.id = t.id
WHERE t.rank2 > 1
OR
DELETE FROM Employee
WHERE Employee.ID
IN
(
SELECT E.ID FROM Employee E
WHERE E.Name IN(
SELECT DISTINCT E1.Name FROM Employee E1
FULL OUTER JOIN Employee E2
ON E1.Name = E2.Name
WHERE E1.Date <> E2.Date
)
AND
E.Date < (
SELECT MAX(Date) FROM Employee
WHERE employee.Name = E.Name
)
)
