To find duplicate rows in table
4.50/5 (2 votes)
Here is an alternative way to find duplicates in a table by making use of over clause http://msdn.microsoft.com/en-us/library/ms189461.aspx[^];with duplicates as (SELECT rowno=row_number() over (partition by colname order by colname), colname, colname from TableName)select * from...
Here is an alternative way to find duplicates in a table by making use of over clause http://msdn.microsoft.com/en-us/library/ms189461.aspx[^]
;with duplicates as (SELECT rowno=row_number() over (partition by colname order by colname), colname, colname from TableName)
select * from duplicates where rowno > 1
The above query also makes it easier to delete duplicates from your table, just replace "select * from" to "delete from" and it will become an effective delete statement.