WITH aaa AS
(
SELECT 1 aa, 1 bb FROM dual
UNION ALL SELECT 1, 2 FROM dual
)
, bbb AS
(
SELECT 1 aa, 1 bb, 1 sn FROM dual
UNION ALL SELECT 1, 1, 2 FROM dual
)
-- 0. 원본 쿼리 그대로 변환하면 오류 발생 
SELECT *
  FROM aaa a
  LEFT OUTER JOIN bbb b
    ON a.aa = b.aa
   AND a.bb = b.bb
   AND b.sn = (SELECT MAX(sn)
                 FROM bbb c
                WHERE aa = b.aa
                  AND bb = b.bb
               )
;
-- ORA-01799: a column may not be outer-joined to a subquery
-- 서브쿼리와 비교하는 방식은 오류 발생되므로 다른 형태로 변경해야 함

-- 1. 비교식을 안으로 넣어 Exists 로 처리하는 방안
SELECT *
  FROM aaa a
  LEFT OUTER JOIN bbb b
    ON a.aa = b.aa
   AND a.bb = b.bb
   AND EXISTS (SELECT 1
                 FROM bbb c
                WHERE aa = b.aa
                  AND bb = b.bb
               HAVING MAX(sn) = b.sn
               )
;

-- 2. 나보다 큰게 없으면 제일 큰거
SELECT *
  FROM aaa a
  LEFT OUTER JOIN bbb b
    ON a.aa = b.aa
   AND a.bb = b.bb
   AND NOT EXISTS (SELECT 1
                     FROM bbb c
                    WHERE aa = b.aa
                      AND bb = b.bb
                      AND sn > b.sn
                   )
;

-- 3. 요즘은 서브쿼리 보다는 ROW_NUMBER 를 더 많이 사용합니다.
SELECT *
  FROM (SELECT a.aa, a.bb, b.sn
             , ROW_NUMBER() OVER(PARTITION BY a.aa, a.bb ORDER BY b.sn DESC) rn
          FROM aaa a
          LEFT OUTER JOIN bbb b
            ON a.aa = b.aa
           AND a.bb = b.bb
        )
 WHERE rn = 1
;