XTqLY499

1row_numb     current_value   value_desired
21
32
43            47              47
54
65            45              45
76
87
98            42              42
109            41              42
1110           40              42
1211           39              42
1312           38              42
1413
1514           36              36
1615
1716
1817           33              33
1918           32              33
20	
21SELECT *
22    , CASE WHEN current_value is not null and LAG(current_value) is null THEN current_value
23           WHEN current_value is not null and LAG(current_value) is not null 
24            THEN LAG(current_value)
25      ELSE NULL END AS value_desired
26  FROM test1
27	
28drop table if exists test_table ;
29create table test_table (row_num int,current_value int);
30insert into test_table(row_num, current_value)
31values
32  (1,null),
33(2,null),
34(3,47),
35(4,null),
36(5,45),
37(6,null),
38(7,null),
39(8 ,42),
40(9 ,41),
41(10,40  ),
42(11,39 ),
43(12,38 ),
44(13,null),
45(14,36),
46(15,null),
47(16,null),
48(17 ,33),
49(18,32  )
50;
51	
52SELECT DISTINCT
53  j1.row_num,
54  CASE WHEN j1.current_value IS NULL
55    THEN NULL
56  ELSE
57    last_value(j2.current_value)
58    OVER (
59      PARTITION BY j1.row_num
60      ORDER BY j2.row_num
61      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) END AS value_desired
62FROM test_table AS j1
63  LEFT JOIN (SELECT
64               row_num,
65               current_value,
66               lag(current_value, 1)
67               OVER (
68                 ORDER BY row_num ) AS prev_cval
69             FROM test_table) AS j2
70    ON j1.row_num >= j2.row_num AND j2.current_value IS NOT NULL
71       AND j2.prev_cval IS NULL
72ORDER BY j1.row_num;