0

I have an Oracle table as Follows:

ID       A      B      C
1       aaa    bbb    ccc
2       aaa    bbb    ccc
3       ddd    eee    fff
4       ddd    eee    fff
5       aha    bqb    ccd
6       aaa    bbb    ccc

I am looking to delete every duplicate row, even if they different IDs.

The intended result is the following:

ID       A      B      C
4       ddd    eee    fff
5       aha    bqb    ccd
6       aaa    bbb    ccc

I am indifferent about keeping the smallest or largest ID.

In other words, i would not mind the following:

ID       A      B      C
1       aaa    bbb    ccc
3       ddd    eee    fff
5       aha    bqb    ccd

Your help is greatly appreciated.

Kelaref
  • 547
  • 1
  • 8
  • 26
  • 1
    I would suggest you create a View, which will just trim the results by grouping them: `create or replace view no_duplicates as select A, B, C, COUNT(*) from Table group by A,B,C having count(*) > 1;` - then you can use the view to create a table with no duplicates etc. is this one time job or it should happen more often? – g00dy Jul 28 '17 at 08:10
  • it's a one time job – Kelaref Jul 28 '17 at 08:12
  • 1
    Not possible for me to test this one: `delete table where rowid > (select min(rowid) from table where (A,B,C) IN (select A, B, C from Table group by A,B,C having count(*) > 1))` - does it do it's job ? – g00dy Jul 28 '17 at 08:16
  • 1
    Corrected and tested: `delete table where rowid <> (select min(rowid) from table where (A,B,C) IN (select A,B,C from table group by A,B,C having count(*) > 1)) and (A,B,C) in (select A,B,C from table group by A,B,C having count(*) > 1)` - Ignore my previous comment. Before committing, try it over two pairs of A,B,C values and verify that is worked. – g00dy Jul 28 '17 at 08:32

0 Answers0