원하시는 쿼리문인지 궁금합니다.

아래 참고하세요.

--- 아래 ---

select a.a
       ,a.b
       ,a.max_c
       ,a.d
       ,a.max_d
from (
       select a.a
              ,a.b
              ,a.c
              ,max(a.c) over (partition by a.a, a.b) max_c
              ,a.d
              ,min(a.d) over (partition by a.a, a.b) min_d
              ,max(a.d) over (partition by a.a, a.b) max_d
       from (
              select 1 a, 'A' b, '' c,   0 d from dual union all                          
              select 1,   'A',   'AAA',  0 from dual union all                         
              select 1,   'B',   '',     0 from dual union all                          
              select 1,   'B',   'BBB',  1 from dual union all                         
              select 1,   'C',   '',     1 from dual union all                          
              select 1,   'C',   'CCC',  1 from dual union all                          
              select 2,   'A',   '',     0 from dual union all
              select 2,   'A',   'AAA',  0 from dual union all                         
              select 2,   'B',   '',     0 from dual union all                         
              select 2,   'B',   'BBB',  1 from dual union all                         
              select 2,   'B',   'BBB',  1 from dual union all                         
              select 2,   'B',   'BBB',  0 from dual
           ) a
    ) a 
 where min_d <> max_d 
 and a.c is not null
 and a.d = 1