CODEWITHSUNDEEP
sqloracle-databaseselectgroup-bygrouping
1131
1131

Reputation: 407

Oracle SQL - Remove double records that differ to one column

I have a select query that returns 30+ fields and I found out that there are some double records that differ to exactly one field. The problem look like:

select t1.field1, t2.field2 
  from table1 t1, table2 t2
 where t1.primary_key = t2.primary_key
 group by t1.field1, t2.field2;

(don't mind the join, it is cross checked and it works fine)

+----+-------+----------+
| id | field1| field2   |
+----+-------+----------+
|  1 |(null) | 20       |
|  2 |SValue | 20       |

In the first the field is null and in the second the field contains a string. I completely want to remove the null record.

I tried to select max(t1.field1) and select nvl(t1.field1) but nothing changed. I also tried to select max(t1.field1) over (partition by t2.field2) but the result was (despite the group by) exactly the same record twice, like:

+----+-------+----------+
| id | field1| field2   |
+----+-------+----------+
|  1 |SValue | 20       |
|  2 |SValue | 20       |

Any suggestion? Thanks in advance

Upvotes: 0

Views: 57

Answers (1)

Andrew Wolfe
Andrew Wolfe

Reputation: 2096

select 
  field1, field2
from (
  select 
    field2, 
    field1,
    rank () over (partition by field2 order by field1 desc nulls last) as fld1_rank,
    coalesce (field1, max (field1) over (partition by field2 order by field1 desc nulls last)) as field1_find,
    count (*) over (partition by field2) as entries_with_field2
  from (
    with 
      t1 as (
        select 't1f1a' as field1, 'pk1' as primary_key from dual
        union all select to_char(null) as field1, '1' as primary_key from dual
        union all select 'SValue' as field1, '2' as primary_key from dual
      ),
      t2 as (
        select 1111 as field2, 'pk1' as primary_key from dual
        union all select 20 as field2, '1' as primary_key from dual
        union all select 20 as field2, '2' as primary_key from dual
      )
    select * 
    from 
      t1 join t2 using (primary_key)
  ) baseresult
  group by
    field1, field2
) ranked
where
  field1 is not null or
  entries_with_field2 = 1
;

Upvotes: 1

Related Questions