Hey dude, If u use SQLSERVER 2005 This will help u. ;WITH tmpEmp AS (

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 / soorai ganesh

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

Answer Posted / soorai ganesh

Hey dude, If u use SQLSERVER 2005 This will help u.

;WITH tmpEmp AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY EmpID ORDER BY EmpID)
AS RowNumber, * FROM EMP
)

DELETE FROM tmpEmp WHERE RowNumber> 1

Is This Answer Correct ?

6 Yes

0 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

How to stop log file growing too big?

1016

What is the default fill factor value?

1082

you have couple of stored procedures that depend on a table you dropped the table and recreated it what do you have to do to reestablish those stored procedure dependencies?

1051

How to automatically create a log when an exception is being received into SQL Server?

1294

how to avoid cursors? : Sql server database administration

974

What is mscorsvw.exe - process - microsoft .net framework ngen?

955

What is implicit mode in sql server?

1109

What is difference between delete & truncate commands?

1085

Explain full-text indexing?

991

How to create a user to access a database in ms sql server using "create user" statements?

956

Explain concepts of analysis services?

963

Explain cdc and sql injection?

984

What is "scheduled jobs" or "scheduled tasks"?

979

Why union all is faster than union?

913

What are the advantages of sql stored procedure?

1076