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

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

How to Check if table exists in sql server?

617

What are the essential components of sql server service broker?

560

Why olap is used?

561

What is implicit mode in sql server?

558

Explain the truncate command?

518




Does hive support indexing?

515

What is the purpose of data source?

511

what is the system function to get the current user's user id? : Sql server database administration

548

How to write the storeprocedure with in the store procedure? and how can we write the store procedure with in a trigger vice versa? plz post me the exact answer?

2111

What is the difference between cube operator and rollup operator? : SQL Server Architecture

618

Explain can SSRS reports Cache results?

112

Explain about Views?

599

What is the most common trace flags used with sql server?

512

How to set database to be read_only in ms sql server?

548

What is triggers in ms sql server?

638