sqldriver (8/27/2014)
I ran into this method in an Itzik Ben-Gan article, which looks more complicated, but of course somehow manages to run a bit faster where I've tested it against a single CTE with ROW_NUMBER() involved, I guess because there's no ORDER BY used anywhere?
;WITH dupe1
AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN ,
[FirstName]
FROM dbo.Emp_Salary
),
dupe2
AS ( SELECT [FirstName] ,
MAX(RN) AS [MAX]
FROM dupe1
GROUP BY [FirstName]
HAVING COUNT(*) > 1
)
SELECT d1.*
--DELETE d1
FROM dupe1 d1
INNER JOIN dupe2 d2
ON d1.[FirstName] = d2.[FirstName]
AND d1.RN < d2.[MAX];
Another consideration on the performance is that this only compares the first name, which multiple people can of course share. The OP did not respond to my suggestion on if what I posted was a good and simple solution. It seems to me that if there is a employeeID in there and no trust issues with the data, then all you have to do is
WITH myCTE
AS (
SELECT row_number() over (partition by EmployeeID
ORDER BY EmployeeID) as Colm1,*
from Employee_salary
)
DELETE From myCTE where Colm1> 1
But since this will very likely be a one time thing (else there is a bigger problem with the system ) , performance will not be as high on the must have list.
----------------------------------------------------