tag:blogger.com,1999:blog-495177919198719500.post8589580155425191685..comments2023-12-27T20:52:29.483-08:00Comments on Microsoft Sql Server Tutorials: Find and Delete Duplicate Rows From Tables without Primary KeySuprotim Agarwalhttp://www.blogger.com/profile/08349831623922214390noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-495177919198719500.post-21429880984816048902010-11-04T09:48:53.928-07:002010-11-04T09:48:53.928-07:00To satisfy the requirement that all dups are delet...To satisfy the requirement that all dups are deleted, no matter how many there are, use the row_number() window function. Number the rows within the same group according to your id field in an ascending order, and then delete all rows that have the generated row_number() > 1. This way you'll be left with no dups and it's all in one go.Unknownhttps://www.blogger.com/profile/17304515752836420756noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-32720653526952273252010-10-18T17:09:25.575-07:002010-10-18T17:09:25.575-07:00This won't work if there is more than one dupl...This won't work if there is more than one duplicate as it only deletes the duplicate with the largest ID field.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-29389692758620961692010-10-18T09:12:06.620-07:002010-10-18T09:12:06.620-07:00Robert adding NOT IN will delete the rows that are...Robert adding NOT IN will delete the rows that are 'not duplicate' and that's exactly the opposite of what the author describes. <br /><br />If you are suggesting an alternate query, please elaborate since I get the desired results using the query shown in this articleFederico Eminoreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-83911196111511506552010-10-18T06:05:04.183-07:002010-10-18T06:05:04.183-07:00I'd change ...WHERE ID IN (...) to ...WHERE ID...I'd change ...WHERE ID IN (...) to ...WHERE ID <b>NOT</b> IN (...), that way you'd get rid of all the duplicates, regardless how many there are.Anonymousnoreply@blogger.com