Skip to main content


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:


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.


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.


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Follow Us