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

    Unanswered: Need to delete duplicate rows and keep two rows

    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
    This might work:
    Code:
    DELETE FROM employee
    WHERE rowid IN (SELECT rowid
                    FROM (
                       SELECT rowid, 
                              row_number() over (partition by first_name, last_name order by first_name) as counter
                       FROM employees
                    ) 
                    WHERE counter > 2)

Posting Permissions

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