CODEWITHSUNDEEP
sqlsql-server
abs786123
abs786123

Reputation: 609

Lag Function to skip over previous record where there is a null

I am trying to get a previous value using the lag function, however it only works for data that is populated on the previous record. What I am looking to do is skip the previous record only if there is a null and look at the previous record prior to that which is not a null

Select LAG(previous_reference_no)OVER(ORDER BY createdon) FROM TableA

So say if I am at record 5,record 4 is null however record 3 is not null. So from record 5 I would want to display the value of record 4.

Hope this makes sense, please help/

Upvotes: 1

Views: 5882

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269523

Standard SQL has the syntax for this:

SELECT LAG(previous_reference_no IGNORE NULLS) OVER (ORDER BY createdon)
FROM TableA

Unfortunately SQL Server does not support this. One method uses two levels of window functions and some logic:

SELECT (CASE WHEN previous_reference_no IS NULL
             THEN MAX(prev_reference_no) OVER (PARTITION BY grp)
             ELSE LAG(previous_reference_no) OVER (PARTITION BY (CASE WHEN previous_reference_no IS NOT NULL THEN 1 ELSE 0 END)
                                                   ORDER BY createdon)
        END)
FROM (SELECT a.*,
             COUNT(prev_reference_no) OVER (ORDER BY a.createdon) as grp
      FROM TableA a
     ) a;

The logic is:

  • Create a grouping that has a given reference number and all following NULL values in one group.
  • If the reference number is NULL, then get the first value for the start of the group. This would be the previous non-NULL value.
  • If the reference number is not NULL then use partition by to look at the last not-NULL value.

Another method -- which is likely to be much slower -- uses APPLY:

select a.*, aprev.prev_reference_no
from TableA a outer apply
     (select top (1) aprev.*
      from TableA aprev
      where aprev.createdon < a.createdon and
            aprev.prev_reference_no is not null
     ) aprev;

For a small table, the performance hit might be worth the simplicity of the code.

Upvotes: 1

Thom A
Thom A

Reputation: 95554

Add a PARTITION BY clause?

Select LAG(previous_reference_no) OVER (PARTITION BY CASE WHEN previous_reference_no IS NULL THEN 0 ELSE 1 END
                                        ORDER BY createdon)
FROM TableA

Upvotes: 1

Related Questions