1. Categories >> Software >> Databases >> SQL Server
  2. Suggest New Category

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

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 the difference between upgrade and migration in sql server?

589

how many bits ip address consist of? : Sql server database administration

456

What do you understand by coalesce in sql server?

495

How to use old values to define new values in update statements in ms sql server?

679

What number aggregate functions are accessible there in sql?

586




What do you mean by sql server agent?

542

What are the system database in sql server 2005?

552

When is the use of update_statistics command?

553

How to list all tables in the database using odbc_tables()?

565

What are subqueries in sql server? Explain its properties.

541

How to backup SQL Server Reporting Services ?

103

Explain hostprotectionattribute in sql server 2005?

597

What are triggers in ms sql server?

599

What do you mean by acid?

572

Issues related in upgrading SQL Server 2000 to 2005 / 2008

1499