LAG and LEAD functions

Syntax:

LAG | LEAD (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )

Window functions LAG and LEAD have appeared in SQL Server in version 2012.

These functions return value of expression calculated for previous (LAG) or next (LEAD) row of the result set respectively. Let’s consider the example of simple query that deduces codes of printers along with codes of previous and next rows:

select  code,
LAG(code) OVER(ORDER BY code) prev_code,
LEAD(code) OVER(ORDER BY code) next_code
from printer;

Notice that if the next or previous row (in the ascending order of code values) does not exist, NULL value is used. However this behavior can be changed with aid of optional argument (the third one) of each function. Value of this argument will be used in the case when the corresponding row does not exist. In the following example the value of -999 is used when the previous row does note. In the following example, the one row is missed.

select  code,
LAG(code,1,-999) OVER(ORDER BY code) prev_code,
LEAD(code,1,999) OVER(ORDER BY code)  next_code
from printer;

To apply the third argument, we have been forced to use the second optional argument with value of 1 which is a default for this argument. This argument defines which row among previous rows (or the next ones) should be used with respect to the current row. In the following example, the second row with respect to current row is used.

select  code,
LAG(code,2,-999) OVER(ORDER BY code) prev_code,
LEAD(code,2,999) OVER(ORDER BY code) next_code
from printer;

Finally note that the order in which the previous or next rows are being picked is given by the ORDER BY clause in OVER clause, but not by the order used for the sorting the query result set. The following query illustrates what was being said.

select  code,
LAG(code) OVER(ORDER BY code) prev_code,
LEAD(code) OVER(ORDER BY code) next_code
from printer
order by code desc;

Let’s consider the “classic” solutions for this task to compare these ones with new approach in SQL.

Self join

select p1.code,p3.code,p2.code
from printer p1 left join Printer p2 on p1.code=p2.code-1
left join Printer p3 on p1.code=p3.code+1;

Correlated subqueries

select p1.code,
(select max(p3.code) from Printer p3 where p3.code < p1.code) prev_code,
(select min(p2.code) from Printer p2 where p2.code > p1.code) next_code
from printer p1;

Suggested exercises: 126, 130, 145