WITH your_table AS (

SELECT  1 AS col1, 'Y' AS col2 FROM dual

union ALL

SELECT  2 AS col1, NULL AS col2 FROM dual

union ALL

SELECT  3 AS col1, NULL AS col2 FROM dual

union ALL

SELECT  4 AS col1, NULL AS col2 FROM dual

UNION ALL 

SELECT  5 AS col1, 'Y' AS col2 FROM dual

union ALL

SELECT  6 AS col1, NULL AS col2 FROM dual

union ALL

SELECT  7 AS col1, NULL AS col2 FROM dual

union ALL

SELECT  8 AS col1, NULL AS col2 FROM dual

)

SELECT 

    col1,

    col2,

    CASE 

        WHEN col2 = 'Y' THEN col1

        ELSE LAST_VALUE(CASE WHEN col2 = 'Y' THEN col1 END IGNORE NULLS) 

             OVER (ORDER BY col1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

    END AS col3

FROM 

    your_table

ORDER BY 

    col1;

with a as(

select rownum a,case when rownum in (1,5,9,16) then 'Y' else '' end  b from dual connect by rownum < 17

)

, t1 as (select a,b,ROWNUM rn from a)

,t2 as (select * from t1 where b='Y')

,t3 as (select t1.rn,max(t2.rn) as rn1 from t1 left join t2 on t1.rn>=t2.rn group by t1.rn)

select t1.a,t1.b,t2.a from t1 left join t3 on t1.rn=t3.rn

left join t2 on t2.rn=t3.rn1