Find duplicate rows in MS SQL

So, the other day I was reading T-SQL: Why “It Depends” and in the comment section a guy suggested using the “PARTITION BY” in the OVER clause.

I thought was the heck does that do ... so i searched the web for an answer and wow, what a great way to find duplicate rows and possible also a lot of other things it can be used for that I haven’t thought about yet.

To the SQL

WITH Data AS
(
	SELECT 
	ROW_NUMBER() OVER ( PARTITION BY CID ORDER BY Added DESC) AS DupeNumber
	,CID, Added
FROM Servers
)
SELECT * FROM Data Where DupeNumber >= 2

This will check for dupes on the CID column … and then select all the rows where DupeNumber is over or equal to 2. You could then instead of select it ... you could delete it.

Fancy way ... I like it.