;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
19564 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

why would you call update statistics? : Sql server database administration

1101

What is the purpose of indexing?

1053

What are the different types of stored procedures?

1123

what is denormalization? : Sql server database administration

938

How to drop an existing table?

1019

Explain different types of BACKUPs avaialabe in SQL Server? Given a particular scenario, how would you go about choosing a backup plan?

1044

What is the difference between left and right outer join?

1079

explain different types of constraints? : Sql server database administration

963

what is the difference between Delete and Truncate command in SQL

1162

What is scan table/view and seek table/view when its occurs? : sql server database administration

1064

What is the sql case statement used for? Explain with an example?

1006

Differentiate between ms sql server reporting services vs crystal reports?

921

How to check if stored procedure is running in sql server?

1074

What is report server project?

149

What is the syntax to execute the sys.dm_db_missing_index_details? : sql server database administration

1166