SQLTeam.com | Weblogs | Forums

Remove duplicates based on 1 field

I have been trying to come up with a way of removing duplicates based on one field without much luck. please see the table below. Notice that CHIC4_COMM is duplicated i.e. it appears next to Addresses 3, 6 and 11. I need the 2 highest numbers i.e. 6 and 11 to be deleted, entire row. Also Names CHIC4_AX-MA, CHIC4_EXET and CHIC4_SGT2, next to Address 21 are duplicated above so these rows also need to be deleted.
Any help appreciated.
|4| CHIC4_AX-MA|
|7| CHIC4_SGT1|
|8| CHIC4_SGT2|
|9| CHIC4_MC1|
|10| CHIC4_MC2|
|11| CHIC4_COMM|
|12| CHIC4_RBS1/2|
|13| CHIC4_MSC2|
|14| CHIC8_SH_REAC2|
|16| CHIC1_COMM|
|17| CHIC1_SGT1|
|18| CHIC1_SGT2|
|19| CHIC4_SH_REAC1|
|20| CHIC8_SGT2|
|21| CHIC4_AX-MA|
|21| CHIC4_EXET|
|21| CHIC4_SGT2|
|22| CHIC4_SH_REAC3|

declare @t table(Address int, Name varchar(20))
insert into @t values

delete t
from @t t
join (
select *, row_number() over (partition by Name order by Address) as RowNum
from @t) x
on t.Address = x.Address
and x.RowNum > 1
select * from @t

1 Like

I prefer the CTE method...

  with cte
    as (
select *, rn = row_number() over(partition by Name order by Address)
  from @t
  from cte
 where rn > 1;

Either method works...

1 Like

Thanks for your suggestions. Will try out later today.

Thanks very much for your replies. I went for the cte version as it was easier to use in my case and it did exactly as I wanted.


just in case ... ( someone interested ) ... different ways to delete dups ( there are lots of ways)


1 Like