CODEWITHSUNDEEP
sqlpostgresql
Apprentice Programmer
Apprentice Programmer

Reputation: 1515

Subquery getting latest column value from rows and collapsing result into a single row

I have a table that outputs this result from the main query, sorted by descending date(latest being the top)

 colA         colb      colc
 ----------------------------
 null         null      null
 True         null      null
 False        null      null
 null         True      null
 null         null      False

I'd like the end result to look like this

 colA     colB   colC
 -----------------------
 True     True   False

One could use a bool_or aggregate function, but it wouldn't work in a case like this:

 colA         colb      colc
 -----------------------------
 null         null      null
 False        null      null
 True         null      null
 null         True      null
 null         null      False

The output of this would be

 colA      colB   colC
 ----------------------
 False     True   False

Basically, I need to traverse every row in each column, and get the earliest value that is not null and return that.

So far I'm using first_value to get window function to get the first value , but unfortunately all rows in the table

select first_value(cola) over() as cola 
from my_data 
where cola is not null;

returns 

  cola
  -----
   f
   f

If i try to do first_value for the other columns, it will not return anything since the amount of rows do no match in each column

What is the best way of doing this?

Upvotes: 0

Views: 69

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125254

with t (date, cola, colb, colc) as ( values
    (current_date, null,  null,  null),
    (current_date - 1, true,  null,  null),
    (current_date - 2, false, null,  null),
    (current_date - 3, null,  true,  null),
    (current_date - 4, null,  null,  false)
)
select
    (array_agg(fv_cola) filter (where fv_cola is not null))[1] as cola,
    (array_agg(fv_colb) filter (where fv_colb is not null))[1] as colb,
    (array_agg(fv_colc) filter (where fv_colc is not null))[1] as colc
from (
    select
        first_value(cola) over(
            partition by cola is null
            order by date desc
        ) as fv_cola,
        first_value(colb) over(
            partition by colb is null
            order by date desc
        ) as fv_colb,
        first_value(colc) over(
            partition by colc is null
            order by date desc
        ) as fv_colc
    from t
) s
;
 cola | colb | colc 
------+------+------
 t    | t    | f

Upvotes: 1

Related Questions