Need to delet duplicate and keep two duplicate records

colloquy84 · #1 (**permalink**) 06-23-11, 02:52

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

shammat · #2 (**permalink**) 06-23-11, 03:18

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)

tonkuma · #3 (**permalink**) 06-23-11, 03:26

Another example:

Code:

DELETE
 FROM  (SELECT ROW_NUMBER()
                  OVER(PARTITION BY First_Name , Last_Name) rn
         FROM  Employee
       )
 WHERE rn > 2
;

shammat · #4 (**permalink**) 06-23-11, 03:37

Cool. Didn't know this was possible in DB2

colloquy84 · #5 (**permalink**) 06-23-11, 04:05

Thanks for the reply. It really solved my issue. Thanks once again.

Arun

Internet Services

Web Development

Digital Marketing

Consumer Tech