DELETE FROM Employee a WHERE ROW_NUMBER() <> ( SELECT MIN( ROW

Delete duplicate rows from a table without primary key by
using a single query
Table Employee
empname salary
A 200
B 300
A 200
C 400
D 500
D 500

Output should be

A 200
B 300
C 400
D 500

Question Posted / arijit mandal

14 Answers
22248 Views
I also Faced
E-Mail Answers

Answer Posted / arijit mandal

DELETE FROM Employee a
WHERE ROW_NUMBER() <>
( SELECT MIN( ROW_NUMBER() )
FROM Employee b
WHERE a.empname = b.empname
AND a.salary = b.salary)

Is This Answer Correct ?

0 Yes

2 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

what is a sub-report?

140

How raid can influence database performance?

1031

how can you select rexcord(rows) from table A which is not present in Table B . Id being the FK in Table B referencing to ID Table A

1682

What is the purpose of forms?

960

What is index, cluster index and nonclustered index?

995

What are locks in sql?

980

What happens if the update subquery returns no rows in ms sql server?

1124

Is sql different from sql server?

1087

Explain what is the function of sql server agent windows service?

994

Explain different types of Normalization.

1833

You want to implement the one-to-many relationship while designing tables. How would you do it?

960

Explain what is raid and what are different types of raid levels?

1027

how you can configure a running aggregate in SSRS?

190

What are the general features of sql server management studio? : sql server management studio

1077

how can a database be repaired? : Sql server administration

971