Find duplicate rows in MS SQL

So, the other day I was reading T-SQL: Why “It Depends” and in the comments section a guys 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 post … and possible also a lot of other things it can be used for that I haven’t thought about.

To the code …

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.