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

42 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    AdminMicrosoft SQL Server (Product Manager, 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
      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