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.