how to delete duplicate rows in table sql server 2005

Posted on 2009-04-21
Last Modified: 2012-05-06
I have 6 identical rows of 4 columns table in sql server 2005
I like to have a script to delete the duplicate rows
Is there a way to list rowid ( oracle concept) along with the columns to delete one of the duplicate rows

Question by:Greens8301
    LVL 25

    Accepted Solution

    See template code below using a temporary table for testing, you should be able to adapt the delete statement below to suit your needs -
    create table #tmp (a int, b int,c int, d int)
    insert #tmp values (0,0,0,0)
    insert #tmp values (1,1,1,1)
    insert #tmp values (2,2,2,2)
    insert #tmp values (3,3,3,3)
    insert #tmp values (4,4,4,4)
    insert #tmp values (5,5,5,5)
    go 2
    select * from #tmp
    delete tmp 
    from	(select *, row_number() over (partition by a,b,c,d order by a,b,c,d) as rn
    	from #tmp) tmp
    where rn > 1
    drop table #tmp

    Open in new window

    LVL 16

    Expert Comment

    You can use a Row Number or a RANK function:

    if you RANK them just in the "partition by" field list ALL the fields then delete where Rank is <> '1'

    Similar for Row
    LVL 7

    Expert Comment

    Delete  from MyTable where ID Not IN
         select  max(ID) from MyTable Group By Duplicate Colum1, Duplicate Column2,Duplicate Column 3
    Table must have identity column which would identify the duplicate records. Here in MyTable ID as identity column  and duplicate columns are duplicate columns 1,2 & 3 (very obvious to know...!!!)
    Also refer :

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now