Removing the Duplicates Values.

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 2000 Forums

Transact-SQL (2000)

Removing the Duplicates Values.

Author

Topic

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-08-28 : 08:47:19

Hi All,

In My table i have records which are duplicate.

i run the following query

Select EmpID,Count(1) From Employee
Group By EmpID
Having Count(1) >1

There were many duplicate empid , so i want remove them..

for eg.
EmpId Count
1044 2
353 2
1059 2
368 2
15 2
338 2
691 2
383 2
308 2
30 2
1014 2
323 2
676 2
751 2
398 2
45 2
1029 2
428 2
263 2
781 2
1119 2

i want to delete only one occurance of this empID. if they are more then one number of time i should be able delete all the rest of them keeping only one occurance so finally my result should come like this when i run above mentioned query.
EmpId Count
1044 1
353 1
1059 1
368 1
15 1
338 1
691 1
383 1
308 1
30 1
1014 1
323 1
676 1
751 1
398 1
45 1
1029 1
428 1
263 1
781 1
1119 1

Thanks

Complicated things can be done by simple thinking

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-08-28 : 10:02:02

there will be some unique key in the table. and if there is then try to apply this:

Begin tran
delete * from employee u1
where u1.uniquekey <> (select max(uniquekey) from employee u2 where u2.empid = u1.empid)

Select EmpID,Count(1) From Employee
Group By EmpID
Having Count(1) >1

Rollback tran

Kapil Arya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources