Interesting demonstration of the ROW_NUMBER() function. Please say it ain’t so, Joe! - that you are not using cursors to remove duplicate rows. Even the technique of a SELECT DISTINCT into a temporary table would be a better option. As other readers have commented, there are a number of ways to remove duplicate rows. This would be my approach:
DELETE Employee
FROM Employee a INNER JOIN (SELECT Empid,
FName,
LName,
MIN(RefDate) AS 'MinDate'
FROM Employee
GROUP BY Empid, FName, LName) b
ON a.Empid = b.Empid
AND a.FName = b.FName
AND a.LName = b.LName
AND a.RefDate > b.MinDate
This would still leave the issue of James verses Jim that would need to be resolved separately. If you didn’t care about spelling variations and wanted to assume that the first entry was the correct one then this would work:
DELETE Employee
FROM Employee a INNER JOIN (SELECT Empid,
MIN(RefDate) AS 'MinDate'
FROM Employee
GROUP BY Empid) b
ON a.Empid = b.Empid
AND a.RefDate > b.MinDate
I would be interested in the question of performance between the two techniques but I’d put my money on mine which I suspect has a whole lot less overhead even as a cross join than having the engine generate a row position.