WITH t AS
(
SELECT 'AAA' material, 'AAA' lot, '901' type, 1000 qty FROM dual
UNION ALL SELECT 'AAA', 'AAA', '309', 1000 FROM dual
UNION ALL SELECT 'BBB', 'BBB', '901', 2000 FROM dual
)
SELECT material, lot, type, qty
  FROM (SELECT material, lot, type, qty
             , ROW_NUMBER() OVER(PARTITION BY material, lot
               ORDER BY DECODE(type, '309', 1, 2)) rn
          FROM t
        )
 WHERE rn = 1
;

그냥 끄젹여 봅니다.

마농님 sql 참고하시고요...

이런 방법도 있다는것만 참고로 봐 주세요.

WITH t AS
(
          SELECT 'AAA' MATERIAL, 'AAA' LOT, '901' TYPE FROM dual
UNION ALL SELECT 'AAA' MATERIAL, 'AAA' LOT, '309' TYPE FROM dual
UNION ALL SELECT 'BBB' MATERIAL, 'BBB' LOT, '901' TYPE FROM dual
)
SELECT a.MATERIAL, a.LOT, a.TYPE from t a
where a.TYPE = '309'
union
SELECT b.MATERIAL, b.LOT, b.TYPE from t b
where b.TYPE = '901'
and  not exists ( SELECT c.MATERIAL, c.LOT, c.TYPE from t c
                 where  c.MATERIAL = b.MATERIAL
                 and    c.lot = c.lot
                 and    c.TYPE ='309' ) 
 

와우....

정말 감사 드려요.... 정말 도움 많이 됐습니다.

모두 복 받으세요.. !!