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.