Cool Stuff in Snowflake – Part 5: Finding Previous Non Null Value

I’m doing a little series on some of the nice features/capabilities in Snowflake (the cloud data warehouse). In each part, I’ll highlight something that I think it’s interesting enough to share. It might be some SQL function that I’d really like to be in SQL Server, it might be something else.

Sometimes you need to find the previous value in a column. Easy enough, the LAG window function makes this a breeze (available since SQL Server 2012). But what if the previous value cannot be null? You can pass a default, but we actually need the previous value that was not null, even if it is a few rows back. This makes it a bit harder. T-SQL guru Itzik Ben-Gan has written about the solution to this problem: The Last non NULL Puzzle. It’s a bit of tricky solution. Suppose we have the following sample data:

We need to find the following data:

See how the value 20 is being repeated, instead of NULL values being returned? The entire solution in T-SQL looks like this:

WITH cte_tmp AS
(
    SELECT
         ID
        ,ColA
        ,grp = MAX(IIF(ColA IS NOT NULL,ID,NULL)) OVER (ORDER BY ID ROWS UNBOUNDED PRECEDING)
    FROM dbo.TestWindow
)
,   cte_nonnull AS
(
    SELECT
         ID
        ,ColA
        ,grp
        ,lastnonnull = MAX(ColA) OVER (PARTITION BY grp ORDER BY ID ROWS UNBOUNDED PRECEDING)
    FROM cte_tmp
)
SELECT
     ID
    ,ColA
    ,grp
    ,lastnonnull
    ,previousnonnull = LAG(lastnonnull) OVER (ORDER BY ID)
FROM cte_nonnull;

I’m not going ever the entire solution, that’s not the point of this blog post. Please refer to Itzik’s article previously mentioned for a detailed explanation (I added the last subquery with the LAG function to get the desired result). Here’s the result set with all the intermediary steps:

So what is the point of this blog post? In Snowflake, we get the same result set with the following code:

SELECT
     ID
    ,ColA
    ,LAG(ColA) IGNORE NULLS OVER (ORDER BY ID) previousnonnull
FROM dbo.TestWindow;

Hmm, this does seems a bit more simplified, doesn’t it? The result:

Cool, right? The IGNORE NULLS clause is supported by many database vendors (even Oracle), but unfortunately not by SQL Server. It would certainly simplify some queries. Actually, you can vote for this feature being added in SQL Server (it was suggested by Itzik himself). Please vote

Other parts in this series:

• Cool Stuff in Snowflake – Part 1: GENERATOR
• Cool Stuff in Snowflake – Part 2: LISTAGG
• Cool Stuff in Snowflake – Part 3: SPLIT and FLATTEN
• Cool Stuff in Snowflake – Part 4: Aliasing all the things