Thanks for your comments folks.

I had forgotten about the SET ROWCOUNT and #tempTable methods . Those worked for all the older versions of SQL and still work now. I just like the new method because it is set based and does not need any extra objects like temp tables to housekeep. I find its easier to read and modify than dynamic sql. Simplicity is in the eye of the beholder, and most people will find the technique they already know to be the "simpler".

I must also acknowledge Tom Huneke's script in http://www.sqlservercentral.com/scripts/CTE/62599/.

I read his script last year and started using the technique, but forgot where the idea came from when I wrote the article.

Jeff Moden's comment is an interesting one. Jeff must have the pleasure of always working with well designed databases.:-P. I do support and troubleshooting of custom developed systems and find the duplicates situation crops up a few times a year. Databases are often designed by application programmers and sometimes by accountants or stock market analysts. The programmers usually know that every table should have a primary key, and dutifully add a unqueidentier or identity column to every table. This does not actually solve all the duplicate problems. I recently found about 6000 duplicate expense records in a mortgage application database even though the table had a primary key on the uniqueidentier. The records should also have been unique on LoanApplicationID and ExpenseTypeID, but a program bug and a lack of another unique constraint allowed the duplicates in.

Cheers, Renato.