;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
19076 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 a database and a data warehouse?

972

Name the different type of indexes in sql?

910

What is cursors?

1100

What do you know about normalization and de- normalization?

873

What are sql dirty pages?

975

Data table as parameter in sql server?

976

Do you know what are acid properties of transaction?

911

What is self contained scalar sub query?

931

What’s the distinction between dropping a info and taking a info offline?

995

What is the purpose of a table?

908

What is the difference between a check constraint and a rule?

1138

What is a trace frag? Where do we use it?

1048

How to connect sql server management studio express to sql server 2005 express?

970

What do you mean by sql server 2005 express management tools?

1257

Explain what are page splits? : SQL Server Architecture

1006