Unanswered: Need to delet duplicate and keep two duplicate records
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.
I think my Oracle solution should work on DB2 as well (using DB2's rid_bit() instead of rowid)
DELETE FROM employee
WHERE rid_bit() IN (SELECT rb
SELECT rid_bit() as rb,
row_number() over (partition by first_name, last_name order by first_name) as counter
WHERE counter > 2)