DECLARE @int as int DECLARE @cnt as int WHILE EXISTS (SELECt top 1 EmpId

I have a table EMP in which the values will be like this

EmpId Ename Sal DeptId
11 Ram 10000 10
11 Ram 10000 10
22 Raj 20000 20
22 Raj 20000 20
33 Anil 15000 30
33 Anil 15000 30

I want to delete only duplicate Rows. After Delete I want
the output like this

EmpId Ename Sal DeptId
11 Ram 10000 10
22 Raj 20000 20
33 Anil 15000 30

Question Posted / siddharth

13 Answers
19520 Views
DELL, I also Faced
E-Mail Answers

Answer Posted / siddharth

DECLARE @int as int
DECLARE @cnt as int
WHILE EXISTS (SELECt top 1 EmpId from Emp group by EmpId having count(EmpId)>1 order by EmpId)
Begin
SELECT top 1 @int=EmpId,@cnt=Count(EmpId) from Emp group by EmpId having count(EmpId)>1 order by EmpId
Delete top (@cnt-1) from Emp where EmpId = @int
End

Select EmpId,Ename,Sal,DeptId from Emp order by EmId

Is This Answer Correct ?

4 Yes

0 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

Explain boyce and codd normal form(bcnf)?

958

Explain error handling in ssis?

1023

What is an indexing technique?

936

What it means to be triggered?

1008

What are various ways to enhance the ssrs report?

981

Can we call future method from trigger?

938

What is the difference between writing data to mirrored drives versus raid5 drives

932

What is system stored procedures?

1040

Do you know how to implement service broker?

928

Can two tables share the same primary key?

916

What is role playing dimension with two examples? : sql server analysis services, ssas

1006

What is fill factor and pad index?

1012

What is an entity-relationship diagram (erd)?

1213

Explain transaction server isolation?

1042

Distinguish between commit and rollback?

961