Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2008
    Posts
    12

    Unanswered: Need to delet duplicate and keep two duplicate records

    Hi All,

    I have a table which has duplicate rows. I need to delete the duplicate rows but I can not simply delete all the the duplicate rows and keep only one copy of the record. My requirment is such that, I need to delete Duplicate rows but need to keep two duplicate rows.

    For example If I have a table Employee with the records as shown below.

    Employee
    -------------------------------
    First_Name Last_Name
    Arun Pandey
    Arun Pandey
    Arun Pandey
    Arun Pandey
    Arun Pandey
    Arun Pandey
    Arun Pandey
    Shweta Tiwari
    Shweta Tiwari
    Shweta Tiwari
    Shweta Tiwari
    Shweta Tiwari

    After running the delete query I should have the below values in the table.

    Employee
    -------------------------------
    First_Name Last_Name
    Arun Pandey
    Arun Pandey
    Shweta Tiwari
    Shweta Tiwari

    I am not able to think of any query which can give me this result. I need help on this.

    Thanks
    Arun

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I think my Oracle solution should work on DB2 as well (using DB2's rid_bit() instead of rowid)
    Code:
    DELETE FROM employee
    WHERE rid_bit() IN (SELECT rb
                        FROM (
                          SELECT rid_bit() as rb, 
                                 row_number() over (partition by first_name, last_name order by first_name) as counter
                          FROM employees
                       ) 
                       WHERE counter > 2)

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another example:

    Code:
    DELETE
     FROM  (SELECT ROW_NUMBER()
                      OVER(PARTITION BY First_Name , Last_Name) rn
             FROM  Employee
           )
     WHERE rn > 2
    ;
    Last edited by tonkuma; 06-23-11 at 04:39. Reason: Removed First_Name , Last_Name in SELECT list.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Cool. Didn't know this was possible in DB2

  5. #5
    Join Date
    Mar 2008
    Posts
    12
    Thanks for the reply. It really solved my issue. Thanks once again.

    Arun

Posting Permissions

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