;with empctc(empid,ename,sal,deptid,ranking) as (Select empid,ename,sal,d

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 / smitha

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

Answer Posted / smitha

;with empctc(empid,ename,sal,deptid,ranking)
as
(Select empid,ename,sal,deptid,ranking=Dense_rank() over (
partition by empid,ename,sal,deptid order by NEWID() asc)
from emp
)
delete * from empctc where ranking>1

Is This Answer Correct ?

2 Yes

0 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

What is SQL Azure?

107

What is a print index?

886

What is the name of the Database which IBM mainframe uses?

987

Explain Reporting Life Cycle?

132

What is a rownum?

980

What is the difference between a fill factor of 100 and 0?

981

What is sqlcmd?

1058

What is a trigger in sql server?

1178

Can a stored procedure call itself or recursive stored procedure? How much level sp nesting is possible?

1027

Explain what are partitioned views and distributed partitioned views?

943

Can sub report data source be different from that of the parent report?

143

How do triggers work?

943

How to create function with parameter in sql server?

1111

What does dml stand for?

952

what is sql server? : Sql server database administration

993