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];