-- 1. Group By Max 후 In 조건으로 제공
SELECT *
  FROM bb
 WHERE (seqno, chasu)
    IN (SELECT seqno
             , MAX(chasu) chasu
          FROM aa
         WHERE seqno = '12345'
         GROUP BY seqno
        )
;

-- 2. Group By Max 후 Join
SELECT a.seqno
     , a.chasu
     , b.value1
  FROM (SELECT seqno
             , MAX(chasu) chasu
          FROM aa
         WHERE seqno = '12345'
         GROUP BY seqno
        ) a
     , bb b
 WHERE a.seqno = b.seqno
   AND a.chasu = b.chasu
;

-- 3. Join 후 Group By Max & Keep
SELECT a.seqno
     , MAX(a.chasu) chasu
     , MAX(b.value1) KEEP(DENSE_RANK LAST ORDER BY a.chasu) value1
  FROM aa a
     , bb b
 WHERE a.seqno = '12345'
   AND a.seqno = b.seqno
   AND a.chasu = b.chasu
 GROUP BY a.seqno
;

-- 4. Group By Max 후 Scalar Sub Query
-- 결과가 다를 수 있음(아우터 조인 효과 있음)
SELECT a.seqno
     , a.chasu
     , (SELECT b.value1
          FROM bb b
         WHERE a.seqno = b.seqno
           AND a.chasu = b.chasu
        ) value1
  FROM (SELECT seqno
             , MAX(chasu) chasu
          FROM aa
         WHERE seqno = '12345'
         GROUP BY seqno
        ) a
;