Home Forums SQL Server 7,2000 T-SQL eliminating duplicates RE: eliminating duplicates

  • July 1, 2008 at 10:55 am

    #836826

    bitbucket (7/1/2008)

    per rbarryyoung:

    Except that that is NOT what was wanted. What was wanted was:

    Eliminate One row from each distinct Group.

    With the Following restrictions:

    No intermediate tables!

    and

    No additional Identity columns

    SQL Server 2000 (not 2005)

    This will delete all non duplicated entries and the first row of a duplicated entry - leaving all other duplicated rows in the table.

    ;with numbered as(SELECT rowno=row_number() over

    (partition by [your column] order by [your column name] ), [your column name] from [your table] )

    delete from numbered where rowno =1

    Heh... RTFS! That won't work in SQL Server 2000 as requested!

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)