Lead and Lag Function in SQL Server 2012

Introduction

SQL Server 2012 introduces two new analytical functions LEAD and LAG. Both the functions access the data from subsequent row and previous row respectively in same result set without self-joining.

LAG Function

Using the LAG function, we can access the data from a previous row in same result set without use of self-join. Return type of LAG function is same as scalar expression. This function returns NULL is scalar expression is null-able or default value is not set.

Syntax

LAG ( scalar expression [ ,offset ] , [ default value] ) OVER ( [ partition by clause ] order by clause )

Scalar expression: The value to be returns based on the passed offset. This is the expression of any scalar type

Offset: It represent number of row forward from the current row. Default value of the offset is 1 and does not have a negative value. If we pass negative value LEAD function threw exception.

Default value: this is value to return when scalar expression at the offset is null. If the default value is not specified then function will return NULL. Note that default value must be type compatible with scalar expression type.

Partition by clause it partitions the result set produced by FROM clause. If it is not specified than function treats all rows of query result set as a single group.

Order by clause use to determine the order of the data before function is applied. If partition by clause is specified, the order by clause determines the order of the data by partition.

Example

Suppose I have table that contain the year wise profit of the company.

CREATE TABLE #YearWiseBusinessData

(

Year INT,

Profit MONEY

)

INSERT INTO #YearWiseBusinessData VALUES(2008,3500)

INSERT INTO #YearWiseBusinessData VALUES(2009,2900)

INSERT INTO #YearWiseBusinessData VALUES(2010,3000)

INSERT INTO #YearWiseBusinessData VALUES(2011,4500)

INSERT INTO #YearWiseBusinessData VALUES(2012,3200)

INSERT INTO #YearWiseBusinessData VALUES(2013,3400)

SQL Table

Now I want to compare previous year profit with the current year profit. In this case LAG function is useful.

SELECT [Year], Profit AS CurrentYearProfit,

LAG(Profit) OVER (ORDER BY [Year]) LastYearProfit

FROM #YearWiseBusinessData

Output

LEAD function

Using LEAD function, we can access the data from subsequent rom of the same result set without self-joining. Return type of LEAD function is same as scalar expression. This function returns NULL is scalar expression is null-able or default value is not set.

Syntax

LEAD ( scalar expression [ ,offset ] , [ default value] ) OVER ( [ partition by clause ] order by clause )

Argument is same as LAG function.

In the previous Example, Now I want to compare any year profit with the next year profit. In this case LEAD function is useful.

SELECT [Year], Profit AS CurrentYearProfit,

LEAD(Profit) OVER (ORDER BY [Year]) NextYearProfit

FROM #YearWiseBusinessData

Combine Example of LEAD and LAG function with offset value

SELECT [Year], Profit AS CurrentYearProfit,

LAG(Profit,1) OVER (ORDER BY [Year]) LastYearProfit,

LEAD(Profit,1) OVER (ORDER BY [Year]) NextYearProfit

FROM #YearWiseBusinessData

LEAD and LAG function

Combine Example of LEAD and LAG function with offset and default value

SELECT [Year], Profit AS CurrentYearProfit,

LAG(Profit,1,0) OVER (ORDER BY [Year]) LastYearProfit,

LEAD(Profit,1,0) OVER (ORDER BY [Year]) NextYearProfit

FROM #YearWiseBusinessData

Conclusion

LEAD and LAG functions are used to retrieve the data from subsequent row and previous row with in same result set without the use of self-join.