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
Answers were Sorted based on User's Feedback
Answer / b.v.rajaram
delete top (select count(*)-(select count(distinct empname )
from Employee) from Employee)from Employee
where a in (select distinct a from Employee)
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / v.krishnakumar
using the keyword distinct we can avoid the duplicate value
in our table,(ex) SELECT DISTINCT empname FROM Employee
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / 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 |
Answer / jansi rani
SELECT EMPNAME,SAL FROM EMP GROUP BY EMPNAME,SAL
Is This Answer Correct ? | 0 Yes | 4 No |
explain different types of cursors? : Sql server database administration
Can we use pragma autonomous_transaction in trigger?
What is “asynchronous” communication in sql server service broker?
what's the difference between delete table and truncate table commands? : Sql server database administration
What are the different ways of moving data/databases between servers and databases in SQL Server?
Explain transaction server isolation?
How do I view a stored procedure in sql server query?
How do you rename a table in sql server?
how to rest identity columns in sql server
WHAT OPERATOR PERFORMS PATTERN MATCHING?
2 Answers CarrizalSoft Technologies, CTS,
How to remove duplicate rows from table except one?
What does Master database contains?