PL/SQl, oracle 9i, deleting duplicate rows using sql

Question

we have a scenario here where we need to delete all the duplicate rows from a table based on timestamp. The table structure looks like this:

Item   Ref1   Ref2        Timestamp
1      A       test1      2/3/2012 10:00:00
1      A       test2      2/3/2012 11:00:00
1      A       test1      2/3/2012 12:00:00
2      A       prod1      2/3/2012 10:00:00
2      B       prod2      2/3/2012 11:00:00
2      A       prod2      2/3/2012 12:00:00

So we need to delete the duplicate rows from this table based on item and ref1. like here we should have only 1 row for item 1 and ref1 A with the latest timestamp. Same for item 2 we should have only 1 row for ref1 A with latest timestamp.

Any pointers will be great

Justin Cave · Accepted Answer · 2012-02-03 18:24:02Z

Assuming that your desired end result is a table with these 3 rows

Item   Ref1   Ref2        Timestamp
1      A       test1      2/3/2012 12:00:00
2      B       prod2      2/3/2012 11:00:00
2      A       prod2      2/3/2012 12:00:00

Something like

DELETE FROM table_name a
 WHERE EXISTS( SELECT 1
                 FROM table_name b
                WHERE a.item = b.item
                  AND a.ref1 = b.ref1
                  AND a.timestamp < b.timestamp );

should work assuming that there are no two rows with the same Item and Ref1 that both have the same Timestamp. If there can be multiple rows with the same Item and Ref1 that both have the latest Timestamp and assuming that you don't care which one you keep

DELETE FROM table_name a
 WHERE EXISTS( SELECT 1
                 FROM table_name b
                WHERE a.item = b.item
                  AND a.ref1 = b.ref1
                  AND a.timestamp <= b.timestamp 
                  AND a.rowid     <  b.rowid);

user800014 · Accepted Answer · 2012-02-03 18:12:05Z

1

You can query your records grouping by Item and Ref1 an then delete where Item and Ref are equals and Timestamp < max.

select Item
     , Ref1
     , max(Timestamp) tm
  from table
 group by Item, Ref1

With the results...

delete from table where Item = ? and Ref1 = ? and Timestamp < ?

answered Feb 3, 2012 at 18:12

user800014

Comments

Lucero · Accepted Answer · 2012-02-03 18:20:07Z

0

I don't have an Oracle 9 install at hand, so I cannot test this, but I believe that this may work:

Create a view which lists adds "indices" to your records:

SELECT ROW_NUMBER( ) OVER (PARTITION BY Item, Ref1 ORDER BY Timestamp DESC) ix, * FROM table
Delete the records from the view where ix is higher than 1

answered Feb 3, 2012 at 18:20

Lucero

60.4k9 gold badges127 silver badges154 bronze badges

Collectives™ on Stack Overflow

PL/SQl, oracle 9i, deleting duplicate rows using sql

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related