Find first non-null values for multiple columns

sql, sql-server

Solution

Using `first_value()`

`first_value(col)` can be used with `and OVER (ORDER BY CASE WHEN col IS NOT NULL THEN sortcol ELSE maxvalue END)`. `ELSE maxvalue` is required because SQL Server sorts nulls first)

CREATE TABLE foo(a int, b int, c int, sortCol int);
INSERT INTO foo VALUES
    (null, 4, 8, 1),
    (1, null, 0, 2),
    (5, 7, null, 3);

Now you can see what we have to do to force nulls to sort after the `sortcol`. To do `desc` you have to make sure they have a negative value.

SELECT TOP(1)
     first_value(a) OVER (ORDER BY CASE WHEN a IS NOT NULL THEN sortcol ELSE 2^31-1 END) AS a,
     first_value(b) OVER (ORDER BY CASE WHEN b IS NOT NULL THEN sortcol ELSE 2^31-1 END) AS b,
     first_value(c) OVER (ORDER BY CASE WHEN c IS NOT NULL THEN sortcol ELSE 2^31-1 END) AS c
FROM foo;

PostgreSQL

PostgreSQL is slightly simpler,

CREATE TABLE foo(a,b,c,sortCol)
AS VALUES
  (null, 4, 8, 1),
  (1, null, 0, 2),
  (5, 7, null, 3);

SELECT
     first_value(a) OVER (ORDER BY CASE WHEN a IS NOT NULL THEN sortcol END) AS a,
     first_value(b) OVER (ORDER BY CASE WHEN b IS NOT NULL THEN sortcol END) AS b,
     first_value(c) OVER (ORDER BY CASE WHEN c IS NOT NULL THEN sortcol END) AS c
FROM foo
FETCH FIRST ROW ONLY;

I believe all of this goes away when RDBMS start to adopt `IGNORE NULLS`. Then it'll just be `first_value(a IGNORE NULLS)`.

Problem

I'm attempting to get the first non-null value in a set of many columns. I'm aware that I could accomplish this using a sub-query per column. In the name of performance, which really does count in this scenario, I'd like to do this in a single pass. Take the following example data: ``` col1 col2 col3 sortCol ==================================== NULL 4 8 1 1 NULL 0 2 5 7 NULL 3 ``` My dream query would find the first non-null value in each of the data columns, sorted on the `sortCol`. For example, when selecting the magical aggregate of the first three columns, sorted by the `sortCol` descending. ``` col1 col2 col3 ======================== 5 7 0 ``` Or when sorting ascending: ``` col1 col2 col3 ======================== 1 4 8 ``` Does anyone know a solution?

Original source