LAG and LEAD Analytical Functions in Sql Server with Real Time Examples

LAG and LEAD Analytical Functions in Sql Server with Real Time Examples

Hello folks, Today I am going to share LAG and LEAD Analytical Functions in Sql Server. After reading this article you will be able to Understand and work with both the Analytical Functions.

1.   LAG Function:-

In SQL Server (Transact-SQL), the LAG function is an analytic function that lets you query more than one row in a table at a time without having to join the table to itself. It returns values from a previous row in the table. 

Syntax

LAG( expression [, offset [, default] ]) 

OVER ( [query_partition_clause ] order_by_clause )

Arguments Description:-

expression

 An expression that can contain other built-in functions, but can not contain any analytic functions.

offset
Optional. It is the physical offset from the current row in the table. If this parameter is omitted, the default is 1.

default 

Optional. It is the value that is returned if the offset goes out of the bounds of the table. If this parameter is omitted, the default is null.

query_partition_clause 

Optional. It is used to partition the results into groups based on one or more expressions.

order_by_clause 

Optional. It is used to order the data within each partition.

Applies To

The LAG function can be used in the following versions of SQL Server (Transact-SQL):

SQL Server 2014, SQL Server 2012

Example:-

Lets take an Example we have following Student data.

And we want to know the previous mark like this.

Another Example would be like we have Customer data with their order date so we want to know the Order Date along with Previous Order date so we would know what was the Previous Order date.

So now to achieve this data we will use LAG function as follow.

SELECT studentName, Mark,

LAG(Mark) OVER( ORDER BY Mark) AS PrevMark

FROM Exam order by Mark

So now it would return above result.

In this example, the LAG function will sort in ascending order all of the Mark values in the Student table.

2.   LEAD Function:-

Accesses data from a subsequent row in the same result set without the use of a self-join in SQL Server 2016. LEAD provides access to a row at a given physical offset that follows the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a following row.

Syntax

LEAD ( scalar_expression [ ,offset ] , [ default ] )  

    OVER ( [ partition_by_clause ] order_by_clause ) 

Arguments Description:-

scalar_expression

The value to be returned based on the specified offset. It is an expression of any type that returns a single (scalar) value. scalar_expression cannot be an analytic function.

offset
The number of rows forward from the current row from which to obtain a value. If not specified, the default is 1. offset can be a column, subquery, or other expression that evaluates to a positive integer or can be implicitly converted to bigint. offset cannot be a negative value or an analytic function.

default
The value to return when scalar_expression at offset is NULL. If a default value is not specified, NULL is returned. default can be a column, subquery, or other expression, but it cannot be an analytic function. default must be type-compatible with scalar_expression.

OVER ( [ partition_by_clause ] order_by_clause)
partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the order of the data before the function is applied. When partition_by_clause is specified, it determines the order of the data in each partition. The order_by_clause is required.

Examples:-

Compare values between years

The query uses the LEAD function to return the difference in sales quotas for a specific employee over subsequent years. Notice that because there is no lead value available for the last row, the default of zero (0) is returned.

Conclusion

In this article I discussed how you can use LAG and LEAD analytical function to access or query data from previous or subsequent rows without writing  self-join query. Write your queries into the comments section.