clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3798208 fiddles created (40976 in the last week).

create table tbl ( id bigint primary key, a int );
 hidden batch(es)


insert into tbl (id, a) select s, case when random() < .5 then s end from generate_series(0, 100) s; select * from tbl;
101 rows affected
id a
0 0
1 1
2 2
3 3
4 4
5
6 6
7 7
8
9
10 10
11 11
12 12
13 13
14
15 15
16 16
17
18 18
19
20 20
21 21
22 22
23
24
25 25
26 26
27
28 28
29
30 30
31
32
33
34 34
35 35
36 36
37 37
38 38
39
40
41
42
43
44 44
45
46
47
48
49
50
51 51
52
53 53
54
55 55
56
57
58 58
59
60
61
62 62
63
64
65
66
67
68
69 69
70
71 71
72
73
74
75 75
76 76
77
78
79
80
81 81
82
83
84 84
85 85
86
87 87
88
89 89
90
91 91
92 92
93
94 94
95 95
96 96
97 97
98 98
99 99
100
 hidden batch(es)


create function coalesce_r_sfunc(state anyelement, value anyelement) returns anyelement immutable parallel safe as $$ select coalesce(value, state); $$ language sql;
 hidden batch(es)


create aggregate find_last_ignore_nulls(anyelement) ( sfunc = coalesce_r_sfunc, stype = anyelement );
 hidden batch(es)


select *, ( select a from tbl it where it.id <= t.id and it.a is not null order by id desc limit 1 ) lnn from tbl t;
id a lnn
0 0 0
1 1 1
2 2 2
3 3 3
4 4 4
5 4
6 6 6
7 7 7
8 7
9 7
10 10 10
11 11 11
12 12 12
13 13 13
14 13
15 15 15
16 16 16
17 16
18 18 18
19 18
20 20 20
21 21 21
22 22 22
23 22
24 22
25 25 25
26 26 26
27 26
28 28 28
29 28
30 30 30
31 30
32 30
33 30
34 34 34
35 35 35
36 36 36
37 37 37
38 38 38
39 38
40 38
41 38
42 38
43 38
44 44 44
45 44
46 44
47 44
48 44
49 44
50 44
51 51 51
52 51
53 53 53
54 53
55 55 55
56 55
57 55
58 58 58
59 58
60 58
61 58
62 62 62
63 62
64 62
65 62
66 62
67 62
68 62
69 69 69
70 69
71 71 71
72 71
73 71
74 71
75 75 75
76 76 76
77 76
78 76
79 76
80 76
81 81 81
82 81
83 81
84 84 84
85 85 85
86 85
87 87 87
88 87
89 89 89
90 89
91 91 91
92 92 92
93 92
94 94 94
95 95 95
96 96 96
97 97 97
98 98 98
99 99 99
100 99
 hidden batch(es)


select *, find_last_ignore_nulls(a) over (order by id) from tbl;
id a find_last_ignore_nulls
0 0 0
1 1 1
2 2 2
3 3 3
4 4 4
5 4
6 6 6
7 7 7
8 7
9 7
10 10 10
11 11 11
12 12 12
13 13 13
14 13
15 15 15
16 16 16
17 16
18 18 18
19 18
20 20 20
21 21 21
22 22 22
23 22
24 22
25 25 25
26 26 26
27 26
28 28 28
29 28
30 30 30
31 30
32 30
33 30
34 34 34
35 35 35
36 36 36
37 37 37
38 38 38
39 38
40 38
41 38
42 38
43 38
44 44 44
45 44
46 44
47 44
48 44
49 44
50 44
51 51 51
52 51
53 53 53
54 53
55 55 55
56 55
57 55
58 58 58
59 58
60 58
61 58
62 62 62
63 62
64 62
65 62
66 62
67 62
68 62
69 69 69
70 69
71 71 71
72 71
73 71
74 71
75 75 75
76 76 76
77 76
78 76
79 76
80 76
81 81 81
82 81
83 81
84 84 84
85 85 85
86 85
87 87 87
88 87
89 89 89
90 89
91 91 91
92 92 92
93 92
94 94 94
95 95 95
96 96 96
97 97 97
98 98 98
99 99 99
100 99
 hidden batch(es)