Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Add language support for null treatment clause (RESPECT NULLS | IGNORE NULLS) with offset window functions (LAG, LEAD, FIRST_VALUE

There�s a common need with offset window functions (LAG, LEAD, FIRST_VALUE, LAST_VALUE) to request to ignore NULLs. A classic example is to return the last non-NULL in an ordered stream of values. Consider the following sample data:

DROP TABLE IF EXISTS dbo.T1;

CREATE TABLE dbo.T1
(
id INT NOT NULL CONSTRAINT PK_T1 PRIMARY KEY,
col1 INT NULL
);

INSERT INTO dbo.T1(id, col1) VALUES
( 2, NULL),
( 3, 10),
( 5, -1),
( 7, NULL),
(11, NULL),
(13, -12),
(17, NULL),
(19, NULL),
(23, 1759);

The id column represents the order of the events, and whenever the col1 value is non-NULL, this represents a value change, and from that point, the new value is considered the applicable one. You want to return the last non-NULL col1 value based on id ordering. For this sample data, the desired result is the following:

id col1 lastnonnull
---- ----- -----------
2 NULL NULL
3 10 10
5 -1 -1
7 NULL -1
11 NULL -1
13 -12 -12
17 NULL -12
19 NULL -12
23 1759 1759

The SQL standard supports a <null treatment> clause with offset window functions, allowing you to specify RESPECT NULLS (default) | IGNORE NULLS. With this clause, the above task would be achieved like so:

SELECT id, col1,
LAG(col1) IGNORE NULLS OVER(ORDER BY id) AS lastnonnull
FROM dbo.T1;

Instead, currently we need to resort to more complex solutions such as this one:

WITH C AS
(
SELECT id, col1,
MAX(CASE WHEN col1 IS NOT NULL THEN id END)
OVER(ORDER BY id
ROWS UNBOUNDED PRECEDING) AS grp
FROM dbo.T1
)
SELECT id, col1,
MAX(col1) OVER(PARTITION BY grp
ORDER BY id
ROWS UNBOUNDED PRECEDING) AS lastnonnull
FROM C;

Thanks much for your consideration!

Cheers,
Itzik

36 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    AdminMicrosoft SQL Server (Admin, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 60

    <=-=Mar 29 2017 11:32PM=-=>

    Just wanted to add that both Oracle and DB2 seem to support this feature:

    http://docs.oracle.com/database/122/SQLRF/LAG.htm#SQLRF00652
    https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_1513.htm

    Cheers,
    Itzik

    <=-=Apr 16 2017 10:08PM=-=>

    That last IBM link was for Informix!

    The DB2 link is https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0023461.html

    <=-=Jul 4 2017 3:30AM=-=>

    Thanks for reporting this request.
    It is in our backlog, and we have planned to do something like this, but we cannot confirm when it would be completed.

    Jovan

    <=-=Jul 10 2017 11:54AM=-=>

    Thanks, Jovan; good to hear.

    2 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • Alexander Sharovarov commented  ·   ·  Flag as inappropriate

        It very useful feature. It would allow the use of LAST_VALUE function for filling gaps in the data. A very common problem in data analysis and data science. If it is difficult to implement for all functions it should at least implemented for the LAST_VALUE function.

      Feedback and Knowledge Base