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 :

delete from dbo.my_tab

from (select row_number() over (order by z.acctproc_id, z.[name], z.acct_id) row,

acctproc_id, name, acct_id

from my_tab z) SOURCE

JOIN

( select a.acctproc_id, a.[name], a.acct_id,

-- a.row,

c.count_row,

max(a.row) row_to_delete

from ( select z.acctproc_id, z.[name], z.acct_id,

row_number() over (order by z.acctproc_id, z.[name], z.acct_id) row

from dbo.my_tab z) a

join

(select b.acctproc_id, b.name, b.acct_id,count(*) count_row

from dbo.my_tab b

group by b.acctproc_id, b.name, b.acct_id

) c

on c.acctproc_id = a.acctproc_id

and c.name = a.name

and (c.acct_id = a.acct_id or c.acct_id is null and a.acct_id is null)

group by a.acctproc_id, a.name, a.acct_id, c.count_row) FILTEREDSET ON

--SOURCE.acctproc_id = FILTEREDSET.acctproc_id and

--SOURCE.[name] = FILTEREDSET.[name]

--and (SOURCE.acct_id = FILTEREDSET.acct_id

-- or Source.acct_id is null and filteredset.acct_id is null)

--and

SOURCE.row= FILTEREDSET.row_to_delete

to get the results we expect.

Where it is using the actual row_numer to perform the inline delete.