Here's one way to do it:
- Group by the columns that you want to check for duplicates
- Return those having a min value < 0 and max value > 0
e.g.:
with rws as (
select 1 id, 'POSONLY' dsc, 1 val from dual union all
select 1 id, 'POSONLY' dsc, 2 val from dual union all
select 2 id, 'POSNEG' dsc, -1 val from dual union all
select 2 id, 'POSNEG' dsc, 1 val from dual union all
select 3 id, 'NEGONLY' dsc, -1 val from dual union all
select 3 id, 'NEGONLY' dsc, -1 val from dual
)
select id, dsc, min(val), max(val)
from rws
group by id, dsc
having min(val) < 0 and max(val)> 0
and count(*) = 2 --optional - only if there's exactly two;
ID DSC MIN(VAL) MAX(VAL)
2 POSNEG -1 1