Sign Up Login


  1. Categories >> Software >> Databases >> SQL Server
  2. Suggest New Category

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

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

Can a cursor be updated? If yes, how you can protect which columns are updated?

723

Will count(column) include columns with null values in its count?

842

How do you run a trace?

686

Explain Geometry datatype in sql server 2008 with example

751

How many clustered indexes there can be on table ?

751




Define outer join?

748

How will you make an attribute not process? : sql server analysis services, ssas

753

How do I open port 1433?

694

Suppose you want to implement the one-to-many relationships while designing tables. How would you do it?

685

what is the Ticketing tool used in Wipro technologies at Bangalore...???

7809

Explain SSRS Architecture?

163

What is awe?

737

what is the difference between Delete and Truncate command in SQL

808

How to apply filtering criteria at group level with the having clause in ms sql server?

743

What is the difference between NOROW and LOCKROW?

2476