SQL SERVER – Introduction to LEAD and LAG

SQL Server 2012 introduces new analytical functions LEAD () and LAG (). These functions accesses data from subsequent row (for lead) and previous row (for lag) in the same result set without the use of a self-join.

The syntax for the Lead and Lag functions is:

Choosing a Global Software Development Partner to Accelerate Your Digital Strategy

To be successful and outpace the competition, you need a software development partner that excels in exactly the type of digital projects you are now faced with accelerating, and in the most cost effective and optimized way possible.

Get the Guide

Note that:
−        “Partition by clause” is optional, “order by clause” is required, and the windowing clause (ROWS|RANGE) is not supported.
−        “Scalar_expression” is the value to be returned – this will normally be a column, but it can also be a sub query or any other expression that results in a single value.
−        “Offset” is the number of previous (LAG) or following (LEAD) rows count from the current row, from which to obtain a value. And if it is not specified, the value will be defaulted to 1.
−        “Default” is the value to be used if the value at the “offset” is NULL, and if it is not specified, the value will be defaulted to NULL.

Example

The following example uses the LAG function to compare year-to-date sales between employees. The PARTITION BY clause is specified to divide the rows in the result set by sales territory. The LAG function is applied to each partition separately and computation restart for each partition. The ORDER BY clause in the OVER clause orders the rows in each partition. The ORDER BY clause in the SELECT statement sorts the rows in the whole result set. Notice that because there is no lag value available for the first row of each partition, the default of zero (0) is returned.