;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
20032 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 are the general features of sql server management studio? : sql server management studio

1136

What is apply operator in sql?

1048

Explain what are the restrictions that views have to follow? : SQL Server Architecture

1106

what are user defined datatypes and when you should go for them? : Sql server database administration

1067

What is an indexing strategy?

1078

What are the fixed server level roles? : sql server security

1227

List down some advantages of sql stored procedure?

1019

How to install sql server 2005 express edition?

1174

What is stored procedures?

1114

Which operator do you use to return all of the rows from one query except rows are returned in a second query?

1047

Tell me can we use custom code in ssrs?

1060

Is sql server free?

978

1.how to find the dead lock in sql server? 2.How to fine the memory leaks in sql server? 3.suppose transaction log file increasing what action will take ?

2084

How many null values we can have in a unique key field in sql server?

1014

What is usually the first word in a sql query?

1178