Results 1 to 2 of 2

Thread: duplicate rows

  1. #1
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    Unanswered: duplicate rows

    What is the best way to remove duplicate rows from a table ?

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy,

    Lets assume the Date column in Maintable has duplicates :

    select Date
    from Maintable
    group by Date
    having count(Date) > 1

    This will select all duplicate dates from the Date column.
    Save these dates into a temp table.

    Then copy all duplicate rows from the original table into a second temp table. Then add an IDENTITY column to this second temp table.

    Now write a routine to delete every second row in the second temp table, using the Identity column to move through the rows....

    Then delete ALL duplicate rows from Maintable .

    Now insert the rows from the second temp table back into Maintable.

    You now have every second ( i.e. no duplicates ) row inserted back into the original table.

    Ugly but robust.....

    Cheers,

    SG.
    Last edited by sqlguy7777; 11-11-03 at 23:09.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •