duplicate records

Question

i want to remove records which are repeated more then once. i have one table having three columns.In this table lot of duplicate records are there. i want to delete all the duplicate records.for example i created one table i put some sample records.please find the sample records. id no sequence 35 35432 1 35 35432 2 35 35432 3 36 35432 1 i want to delete all the records of id=35.i want the record of only id=36.

ramji · Answer

thanks

sanjay pawar · Answer

;WITH tempRecord AS (select ROW_NUMBER() over(partition by column1 column2column3)as RowNumber * from #tmp_tbl2) delete tempRecord where Rownumber >1

ramji · Answer

is it possible to check both columns in where condition where id and no.because some times id no sequence 35 35432 1 35 35432 2 35 35432 3 36 45232 1 35 44461 1 in this above example in this last row id value 35 is repeated but no column value is 44461 is different.so i do not want to delete that record. delete all the records which contains id and no columns values are same.

Raja T · Answer

Hi please check out bellow URLs http://www.dotnet-tricks.com/Tutorial/sqlserver/IL3S290812-Remove-duplicate-records-from-a-table-in-SQL-Server.html http://www.besttechtools.com/articles/article/sql-query-to-delete-duplicate-rows http://www.codeproject.com/Articles/157977/Remove-Duplicate-Rows-from-a-Table-in-SQL-Server

Manas Mohapatra · Answer

Try below query: DELETE FROM [TableName] WHERE id IN( SELECT id FROM [TableName] GROUP BY id HAVING COUNT(id) > 1)

ramji · Answer

lot of duplicate records are there.so i cannot specify the id=35.for particular record.lot of ids are duplicated.so i want to delete all the records

Raja T · Answer

Hi Ramji delete from tbl_name where id=35