WITH t AS
(
SELECT 1 a, 1 b FROM dual
UNION ALL SELECT 2, 0 FROM dual
UNION ALL SELECT 3, 1 FROM dual
UNION ALL SELECT 4, 1 FROM dual
UNION ALL SELECT 5, 0 FROM dual
UNION ALL SELECT 6, 1 FROM dual
UNION ALL SELECT 7, 1 FROM dual
)
SELECT a, b
     , LAST_VALUE(c IGNORE NULLS) OVER(ORDER BY a DESC) c
  FROM (SELECT a, b
             , DECODE(b, 1, LEAD(a) OVER(PARTITION BY b ORDER BY a)) c
          FROM t
        )
 ORDER BY a
;

9i 에선 ignore nulls 가 안되는 군요.

WITH t AS
(
SELECT 1 a, 1 b FROM dual
UNION ALL SELECT 2, 0 FROM dual
UNION ALL SELECT 3, 1 FROM dual
UNION ALL SELECT 4, 1 FROM dual
UNION ALL SELECT 5, 0 FROM dual
UNION ALL SELECT 6, 1 FROM dual
UNION ALL SELECT 7, 1 FROM dual
)
SELECT a, b, c
     , MAX(a)
       OVER(ORDER BY c
            RANGE BETWEEN '1' PRECEDING AND '1' PRECEDING) c
  FROM (SELECT a, b
             , SUM(b) OVER(ORDER BY a DESC) c
          FROM t
        )
 ORDER BY a
;

아 마농님 감사합니다..

그리고 늦엇지만 몇달전 세미나 넘 잘들었씁니다.^^;

저두 LEAD함수 사용하면 되지않을까 하는 고민은 해ㅅ봣지만.. 아직은 배워야할게 넘 많네요..

감사합니다.