Jeff Moden (7/1/2008)
You guys are missing the point of this particular exercise... despite the name of the post, this is NOT about the elimination of duplicates. Here's what the op wanted...1. Remove 1 row from any group of dupes.
2. Remove all rows that are not dupes.
3. Cannot use any form of intermediate table. No table variable, no temp table, not even the working table formed by a cursor.
4. Cannot add any columns to the original table.
Zackly. Get rid of everything which would stay if it was a dedupe.
It's a NOT dedupe! 😀
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden