Conditional duplicate removal in Oracle 10g

Question

CREATE TABLE testdup
(seq_no NUMBER,
ID NUMBER,
attrib1 NUMBER,
attrib2 NUMBER);

INSERT INTO testdup
     VALUES (2, 15, 1211, 1250);
INSERT INTO testdup
     VALUES (1, 15, -999, -999);
INSERT INTO testdup
     VALUES (3, 16, 1234, 1234);
INSERT INTO testdup
     VALUES (4, 16, 1234, -1234);
INSERT INTO testdup
     VALUES (5, 17, -999, -999);
INSERT INTO testdup
     VALUES (6, 17, -999, -999);
INSERT INTO testdup
     VALUES (7, 18, -999, -999);
INSERT INTO testdup
     VALUES (8, 19, 741, -715);

COMMIT ;

What I need to do is, delete duplicate ids -

• If duplicate ids records are having values (attrib1= any value except -999) then delete lower seq_no (in above example id=16)
• If -999 is present along with non -999 (in above example id=15) then delete record having value as -999
• if for duplicate records values 'attrib1=-999' is present for all records then delete lower seq_no (in above example id=17)
• skip non duplicates ids (in above example id=18 and 19)

In above example below seq_no should be deleted 1, 3 and 5

Database - Oracle 10g

Below query is giving me partial output but when attrib1 are same for the given id then deleting min(seq_id) is not working out

SELECT seq_no, ID, attrib1,
       ROW_NUMBER () OVER (PARTITION BY ID, ID ORDER BY CASE
           WHEN attrib1 = -999
              THEN 999999999
           ELSE TO_NUMBER (attrib1)
        END) rn
  FROM testdup order by 1

I was playing around with analytic function and found solution for this, attaching it here for other peoples reference

SELECT seq_no, ID, attrib1,
       ROW_NUMBER () OVER (PARTITION BY ID ORDER BY CASE
           WHEN attrib1 = -999
              THEN 999999999
           ELSE TO_NUMBER (attrib1)
        END ASC,
        seq_no DESC) rn
  FROM testdup

Answer 1

Here is a try:

with w as
(
  select t.id,
         case when sum(case when t.attrib1 = -999 then 1 else 0 end) > 0 then 1 else 0 end exists999,
         case when min(t.attrib1) = -999 and max(t.attrib1) = -999 then 1 else 0 end only999
  from testdup t
  group by t.id
  having count(*) > 1
)
select 'Only -999 values, removed min seq_no' reason, min(t.seq_no) removed
from testdup t, w
where w.id = t.id
  and w.only999 = 1
group by t.id

union all

select 'No -999 values, removed min seq_no' reason, min(t.seq_no) removed
from testdup t, w
where w.id = t.id
  and w.exists999 = 0
group by t.id

union all

select 'Some -999 values, removed seq_no with this value' reason, t.seq_no removed
from testdup t, w
where w.id = t.id
  and w.exists999 = 1
  and w.only999 = 0
  and t.attrib1 = -999
;

The with clause allows me to know in a group of similar IDs, if they contain only -999 values or not. Then I have one query per criteria you have.

The result:

    REASON                                 REMOVED
1   Only -999 values, removed min seq_no                5
2   No -999 values, removed min seq_no                  3
3   Some -999 values, removed seq_no with this value    1

Answer 2

I believe you want

delete from testdup where rowid in (
  select
    coalesce(
      case when rowid_999_min is not null and 
                rowid_999_max is not null and
                rowid_999_min != rowid_999_max 
           then null
           else rowid_999_max
      end,
      rowid_min_seq
  )
  from (
      select
        min(case when attrib1 = -999 then rowid end)        rowid_999_min,
        max(case when attrib1 = -999 then rowid end)        rowid_999_max,
        min(rowid) keep (dense_rank first order by seq_no)  rowid_min_seq
      from
        testdup
      group by
        id
      having
        count(*) > 1
  )
);