Duplicate Entries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Duplicate Entries

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-03-05 : 22:52:28

Hi Forumer's

Upn checking from my table I just encounter about 362 records with duplicate entries. I just want retain 1 records of the duplicate records. kindly please help me to modify the scripts below the suit to my requirements.


--DATA
declare @tmp table
(esn nvarchar(35), model_code nvarchar(35), supplier int,  shipdate datetime)
insert into @tmp values('270113179916697013','P333-4410-EVO',40,'2011-09-28 00:00:00.000')
insert into @tmp values('270113179916697013','P333-4410-EVO',40,'2011-09-28 00:00:00.000')
insert into @tmp values('268435456203849506','P300-1710-DROID',45,'2011-08-23 00:00:00.000')
insert into @tmp values('268435456203849506','P300-1710-DROID',45,'2011-08-23 00:00:00.000')
insert into @tmp values('268435456203849508','P300-1710-DROID2',42,'2011-09-20 00:00:00.000')


--SCRIPT
select 
      ESN,
      shipdate,
      COUNT(ESN) as total
from @tmp
group by 
ESN,
shipdate
having COUNT(ESN) > 1
Order by ESN