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

Book Review – Microsoft Power BI Performance Best Practices

I was asked to do a review of the book Microsoft Power BI Performance Best…

5 days ago

Create a Numbers Table in Power Query

This is a quick blog post, mainly so I have the code available if I…

1 week ago

Microsoft finally adds Tenant Switcher for Fabric / Power BI

Praise whatever deity you believe in, because it's finally here, a tenant switcher for Microsoft…

1 week ago

Book Review – Humanizing Data Strategy by Tiankai Feng

This book was making its rounds on social media, and the concept seems interesting enough…

2 weeks ago

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.…

4 weeks ago

dataMinds Connect 2024 – Session Materials

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

2 months ago