clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2805438 fiddles created (40822 in the last week).

select version();
version
PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit
 hidden batch(es)


CREATE TABLE tab ( version int primary key, col_A TEXT, col_B TEXT, col_C TEXT );
 hidden batch(es)


insert into tab (version, col_A, col_B, col_C) select i, case when random() < 0.5 then 'A' || floor(random()*10::int) end, case when random() < 0.5 then 'B' || floor(random()*10::int) end, case when random() < 0.5 then 'C' || floor(random()*10::int) end from generate_series(1, 99999) as g(i) ;
99999 rows affected
 hidden batch(es)


create index ix_va on tab (version) WHERE (col_a IS NOT NULL) ; create index ix_vb on tab (version) WHERE (col_b IS NOT NULL) ; create index ix_vc on tab (version) WHERE (col_c IS NOT NULL) ; create index ix_version_include_a on tab (version) INCLUDE (col_a) WHERE (col_a IS NOT NULL) ; create index ix_version_include_b on tab (version) INCLUDE (col_b) WHERE (col_b IS NOT NULL) ; create index ix_version_include_c on tab (version) INCLUDE (col_c) WHERE (col_c IS NOT NULL) ; create index tag_version_desc_x on tab (version desc);
 hidden batch(es)


CREATE OR REPLACE FUNCTION test_get_lnn() RETURNS TABLE(col_a text, col_b text, col_c text) LANGUAGE plpgsql AS $function$ declare t_a text := null; t_b text := null; t_c text := null; r record; begin for r in select t.col_A, t.col_B, t.col_C from tab t order by version desc loop if t_a is null then t_a := r.col_A; end if; if t_b is null then t_b := r.col_B; end if; if t_c is null then t_c := r.col_C; end if; exit when t_a is not null and t_b is not null and t_c is not null; end loop; return query select t_a, t_b, t_c; end; $function$
 hidden batch(es)


SELECT ( SELECT col_a FROM tab WHERE col_a IS NOT NULL ORDER BY version DESC LIMIT 1 ), ( SELECT col_b FROM tab WHERE col_b IS NOT NULL ORDER BY version DESC LIMIT 1 ), ( SELECT col_c FROM tab WHERE col_c IS NOT NULL ORDER BY version DESC LIMIT 1 ), current_time, 'Quassnoi Option 1' author;
col_a col_b col_c current_time author
A0 B7 C3 13:57:52.484423+00 Quassnoi Option 1
 hidden batch(es)


SELECT a.col_a, b.col_b, c.col_c, current_time, 'Quassnoi Option 2' author FROM ( SELECT last_a, last_b, last_c FROM ( SELECT *, MAX(version) FILTER (WHERE col_a IS NOT NULL) OVER w AS last_a, MAX(version) FILTER (WHERE col_b IS NOT NULL) OVER w AS last_b, MAX(version) FILTER (WHERE col_c IS NOT NULL) OVER w AS last_c FROM tab WINDOW w AS (ORDER BY version DESC) ) q WHERE (last_a, last_b, last_c) IS NOT NULL ORDER BY version DESC LIMIT 1 ) q JOIN tab a ON a.version = q.last_a JOIN tab b ON b.version = q.last_b JOIN tab c ON c.version = q.last_c ;
col_a col_b col_c current_time author
A0 B7 C3 13:57:52.486065+00 Quassnoi Option 2
 hidden batch(es)


SELECT DISTINCT FIRST_VALUE(col_A) OVER (ORDER BY CASE WHEN col_A IS NULL THEN 0 ELSE version END DESC) AS LastA, FIRST_VALUE(col_B) OVER (ORDER BY CASE WHEN col_B IS NULL THEN 0 ELSE version END DESC) AS LastB, FIRST_VALUE(col_C) OVER (ORDER BY CASE WHEN col_C IS NULL THEN 0 ELSE version END DESC) AS LastC, current_time, 'J.D.' author FROM tab ;
lasta lastb lastc current_time author
A0 B7 C3 13:57:52.48715+00 J.D.
 hidden batch(es)


select *, current_time, 'Gerard H. Pille' author from test_get_lnn();
col_a col_b col_c current_time author
A0 B7 C3 13:57:55.114116+00 Gerard H. Pille
 hidden batch(es)