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 what are the events recorded in a transaction log?
1.what is the diff between nolock optimizer and read uncommitted isolation? 2.what is the diff between revoke and deny? 3.what is percieved down time? 4.whether password protection are required for backups?if yes why?if no why? 5.what is fill factor? 6.what is cost analysis? 7.what is mean by piece meal restore? 8.what is 'rowguidcol'? 9.impersonate permission? 10.what is selectivity?
Explain the difference between function and stored procedure?
What are the joins in sql server? : sql server database administration
How to create function with parameter in sql server?
how would you improve etl (extract, transform, load) throughput?
What is sql service broker?
What is an extended stored procedure? Can you instantiate a COM object by using T-SQL?
What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand?
What's the difference between a primary key and a unique key?
How to list all tables in the database using odbc_tables()?
What is difference between table aliases and column aliases? Do they affect performance?