Remove duplicates based on 1 field

Transact-SQL
1

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.
|Address|Name|
|1| CHIC4_SITE|
|2| CHIC1_ATCC|
|3| CHIC4_COMM|
|4| CHIC4_AX-MA|
|5| CHIC4_EXET|
|6| CHIC4_COMM|
|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|

2

declare @t table(Address int, Name varchar(20))
insert into @t values
(1,'CHIC4_SITE'),
(2,'CHIC1_ATCC'),
(3,'CHIC4_COMM'),
(4,'CHIC4_AX-MA'),
(5,'CHIC4_EXET'),
(6,'CHIC4_COMM'),
(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')

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
3

I prefer the CTE method...

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

Either method works...

1 Like
4

Thanks for your suggestions. Will try out later today.

5

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.

6

hi

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

https://www.sqlshack.com/different-ways-to-sql-delete-duplicate-rows-from-a-sql-table/

1 Like