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:

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

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.