Categories: Varia

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:


------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂
Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at AE, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.

Recent Posts

Free webinar – Tackling the Gaps and Islands Problem with T-SQL Window Functions

I'm hosting a free webinar at MSSQLTips.com at the 19th of December 2024, 6PM UTC.…

5 days ago

dataMinds Connect 2024 – Session Materials

The slides and scripts for my session "Tackling the Gaps & Islands Problem with T-SQL…

4 weeks ago

Connect to Power BI as a Guest User in another Tenant

Sometimes your Microsoft Entra ID account (formerly known as Azure Active Directory) is added as…

2 months ago

How to use a Script Activity in ADF as a Lookup

In Azure Data Factory (ADF, but also Synapse Pipelines and Fabric Pipelines), you have a…

4 months ago

Database Build Error – Incorrect syntax near DISTINCT

I wrote a piece of SQL that had some new T-SQL syntax in it: IS…

4 months ago

Speaking at dataMinds Connect 2024

I'm very excited to announce I've been selected as a speaker for dataMinds Connect 2024,…

5 months ago