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
ROW_NUMBER() OVER ( PARTITION BY CID ORDER BY Added DESC) AS DupeNumber
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.