How to retrieve duplicate rows in a table?
How to delete the duplicate entries in a table?
Answer Posted / madhur/amrutha
select ROW_NUMBER() OVER (ORDER BY names ASC) AS ROWID, *
into #temp from emp
select * from #temp where ROWID not in(
select b.ROWID from
(
select ROW_NUMBER() OVER (ORDER BY names ASC) AS ROWID, *
from emp
except
SELECT ROW_NUMBER() OVER (ORDER BY names ASC) AS ROWID, *
FROM
(
select names , sal from emp
union
select distinct names,sal from emp) as a ) as b)
drop table #temp
Is This Answer Correct ? | 3 Yes | 0 No |
Post New Answer View All Answers
Write a SQL query to make a column as unique?
Difference between connected and disconnected database in .net with sql server?
Which tcl commands are available on the sql server?
How to replace the Query Result 'Null Value' with a text ?
What is the name of the Database which IBM mainframe uses?
What is inner join in sql server joins?
Explain how dts is used to extract, transform and consolidate data?
Can a database be shrunk to 0 bytes, if not, why?
What is the maximum size of a row in sql server?
can a database be shrunk to 0 bytes, if not, why? : Sql server administration
What are the difference between primary key and unique key? : sql server database administration
What is your recommendation for a query running very slow? : sql server database administration
How do I view views in sql server?
What does it mean if @@cursor_row returns a negative number?
What is blocking and how would you troubleshoot it? : sql server database administration