d_sysuk (7/1/2008)LEAVES ONLY 1 COPY of ANY DUplicate ROW as wanted !!!

Except that that is NOT what was wanted. What was wanted was:

Eliminate One row from each distinct Group.

With the Following restrictions:

No intermediate tables!andNo additional Identity columns

SQL Server 2000 (not 2005)

And, yes, we all know that it can be easily be done with Cursors or While loops. In fact, it seems like the point of this challenge was to try to demonstrate something that could only be done with Cursors or other kinds of loops. Therefore there is one additional consideration that you might as well add:

Well going to the first post, its clear the the post is most unclear in its requirements. Shouting at posters wont help under any circumstances or imposing random conditions , e.g. its now SQL 2000, not SQL 2005 etc..

There is something that makes posts clear, test data , source input / output - makes life easier to understand than a bunch of words.

A test-framework helps any post, users know what the goals are..Anyways.. here is A LOOP ! - But it does whats required..

-- DELETE ALL Rows which are NOT Duplicates !delete from my_tab from my_tab a -- Get first duplicate record for delete join (select top 1 acctproc_id, [name], acct_id from dbo.my_tab group by acctproc_id, name, acct_id having count(*) =1 ) b on -- Cope with a NULL Join condition for row where null possible on acct_id (a.acct_id = b.acct_id or a.acct_id is null and b.acct_id is null) and a.name = b.name and a.acctproc_id = b.acctproc_id

select @myvarloop-- Start our loop deleting the dup rows-- set rowcount 1 -- ensure we only delete 1 row, cannot delete the two dup records-- We'll use the TOP Clause here, for a SQL 2005 mechanism, SQL 2000, use set rowcount 1 delete top (1) from my_tab from my_tab a -- Get first duplicate record for delete join (select top 1 acctproc_id, [name], acct_id, row_number() over (order by acctproc_id, name, acct_id) row from dbo.my_tab group by acctproc_id, name, acct_id having count(*) > 1) b on -- Cope with a NULL Join condition for row where null possible on acct_id (a.acct_id = b.acct_id or a.acct_id is null and b.acct_id is null) and a.name = b.name and a.acctproc_id = b.acctproc_id and row = @myvarloop

As far as a framework, Jeff Moden posts this link in his signature (and I believe others do as well). It makes it very clear what should be posted to receive quicker, more accurate responses. As far as SQL2000 vs. SQL2005, sometimes it's critical as there are changes/additions to functionality depending on version, correct? Sometimes posts are posted to the wrong forum and if people are thinking it's 2005, that's what most - if not all, responses will be geared toward. I don't like wasting my time trying to do something one way and find out, due to version, it's not going to work. And yes, sometimes, shouting at posters is the only way to be HEARD. The important thing is that people are giving their time, and talent, freely here to try and help someone out. Sometimes the OP also has to help themselves....IMHO

To the lovely Shawn: d_sysuk: dude you just don't get it do you?--> Stop posting, we're fed up:..had enough of this yesterday.Advice: STOP READING POST !

I like many others do not live on USA timezones, picked this question up my time today.

Had a look at this only for a couple of hours, but this highlights an interesting issue - almost valid of a MS connect feedback scenario - "To allow row_number deletes to apply - not to delete ALL duplicate entrys" ..., where it would be achieve a single SQL 2005 statement version :