SQL Find the first non null value with arbitrary order by

user_1589188 0
Viewed 17451

Given the following table:

ID | Value
----------
 1 | NULL
 2 |  B
 3 |  C
 4 | NULL

I want to get the first non-null value from the Value column according to different ordering, e.g.:

SELECT FIRST_NON_NULL(Value ORDER BY ID) FROM MY_TABLE

This will return B

SELECT FIRST_NON_NULL(Value ORDER BY ID DESC) FROM MY_TABLE

This will return C

P.S. Doesn't have to be a value function, just the SELECT statement that is needed. Thank you.

Edited: Want to see if there can be a scalable version.

ID | Value1 | Value2
--------------------
 1 |  NULL  |   AA
 2 |   B    |  NULL
 3 |   C    |   CC
 4 |  NULL  |  NULL

SELECT FIRST_NON_NULL(Value1, Value2 ORDER BY ID) FROM MY_TABLE

This will return B, AA

SELECT FIRST_NON_NULL(Value1, Value2 ORDER BY ID DESC) FROM MY_TABLE

This will return C, CC

sql-server-2008-r2 tsql sql-server sql
4 Answers

Original answer is definitely the best for the provided example.

For those out there who need to do this within particular partitions and at a large volume, this becomes problematic and intensive, as it can translate into a huge number of individual table hits on the back end.

I have a solution using window functions that solves the problem for large data / partitions.

Expanding the original example data as follows - let's have two sets in the data, based on GroupID (1 and 2):

GroupID | ID | Value1 | Value2
--------------------------
1       |  1 |  NULL  |   AA
1       |  2 |   B    |  NULL
1       |  3 |   C    |   CC
1       |  4 |  NULL  |  NULL
2       |  5 |   E    |   EE
2       |  6 |   F    |  NULL
2       |  7 |  NULL  |   GG
2       |  8 |  NULL  |  NULL

And I want to provide these first/last non-null values for each GroupID value, like this:

GroupID | FirstValue1 | FirstValue2 | LastValue1 | LastValue2
-------------------------------------------------------------
1       | B           | AA          | C          | CC
2       | E           | EE          | F          | GG

If I apply the top 1 logic to scale, and I have lots of rows, it's reaching back to perform tons of individual table queries. However, if I use window functions, I can have it evaluate data it has in memory to more efficiently get the data, esp when partitions are needed and lots of data.

Here is the solution:

SELECT
  GroupID,
  FirstValue1,
  FirstValue2,
  LastValue1,
  LastValue2
FROM
  (
  SELECT
    GroupID,
    ID, Value1, Value2,
    -- Sets a rank so we can reduce to 1 row per GroupID in outer query
    DENSE_RANK() OVER (
      PARTITION BY GroupID
      ORDER BY ID
    ) IDRank,
    FIRST_VALUE(Value1) OVER (
      PARTITION BY GroupID
      ORDER BY
        CASE WHEN Value1 IS NULL THEN 2 ELSE 1 END, -- Prioritize non-null Value1
        ID                                          -- And ascending ID
    ) FirstValue1,
    FIRST_VALUE(Value2) OVER (
      PARTITION BY GroupID
      ORDER BY
        CASE WHEN Value2 IS NULL THEN 2 ELSE 1 END, -- Prioritize non-null Value2
        ID                                          -- And ascending ID
    ) FirstValue2,
    FIRST_VALUE(Value1) OVER (
      PARTITION BY GroupID
      ORDER BY
        CASE WHEN Value1 IS NULL THEN 2 ELSE 1 END, -- Prioritize non-null Value1
        ID DESC                                     -- And descending ID
    ) LastValue1,
    FIRST_VALUE(Value2) OVER (
      PARTITION BY GroupID
      ORDER BY
        CASE WHEN Value2 IS NULL THEN 2 ELSE 1 END, -- Prioritize non-null Value2
        ID DESC                                     -- And descending ID
    ) LastValue2
  FROM MY_TABLE
  ) BestValues
WHERE IDRank = 1  --Ensures we get only one row per GroupID
ORDER BY GroupID

Explained - FIRST_VALUE evaluates within each distinct GroupID (since we set PARTITION BY GroupID). Within that window, it evaluates, of the rows for that GroupID, cases with non-null values, and then ordering by ID (ascending or descending). As it is a window function, this puts the value on the level of the records in their original granularity, so we also add a DENSE_RANK to rank and give us the ability to, in the outer query, get down to one row per GroupID.

p.s., if you would like to run it yourself, include the following above the query to generate the needed sample data into "MY_TABLE" in a CTE.

WITH MY_TABLE AS (
  SELECT 0 as GroupID, 0 AS ID, CAST(NULL AS VARCHAR(10)) as Value1, CAST(NULL AS VARCHAR(10)) as Value2 WHERE 0=1 UNION ALL --Dummy row to set types

  SELECT 1 AS GroupID, 1 AS ID, NULL AS Value1, 'AA' AS Value2 UNION ALL
  SELECT 1 AS GroupID, 2 AS ID, 'B'  AS Value1, NULL AS Value2 UNION ALL
  SELECT 1 AS GroupID, 3 AS ID, 'C'  AS Value1, 'CC' AS Value2 UNION ALL
  SELECT 1 AS GroupID, 4 AS ID, NULL AS Value1, NULL AS Value2 UNION ALL
  SELECT 2 AS GroupID, 5 AS ID, 'E'  AS Value1, 'EE' AS Value2 UNION ALL
  SELECT 2 AS GroupID, 6 AS ID, 'F'  AS Value1, NULL AS Value2 UNION ALL
  SELECT 2 AS GroupID, 7 AS ID, NULL AS Value1, NULL AS Value2 UNION ALL
  SELECT 2 AS GroupID, 8 AS ID, NULL AS Value1, 'GG' AS Value2
)
user_31551530

In MySQL and I believe most versions you can use the Coalesce function, it simply returns the first non-null value in the list.

Your query would end up being

SELECT COALESCE(Value ORDER BY ID) FROM MY_TABLE

Example here

user_102470390
Related