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:
I was asked to do a review of the book Microsoft Power BI Performance Best…
This is a quick blog post, mainly so I have the code available if I…
Praise whatever deity you believe in, because it's finally here, a tenant switcher for Microsoft…
This book was making its rounds on social media, and the concept seems interesting enough…
I'm hosting a free webinar at MSSQLTips.com at the 19th of December 2024, 6PM UTC.…
The slides and scripts for my session "Tackling the Gaps & Islands Problem with T-SQL…