Lag Function In SQL Server

In this article, we will learn about the Lag() function in SQL servers.

• The Lag() function is a window function. It was introduced in SQL server 2012 and onwards.
• The Lag() function is used to access the row from the specified offset, which comes before the current row.
• In other words, by using the Lag() function we can access the previous row from the current row or the row before the previous row, and so on.
• The Lag() function is useful when we want to compare a value of the current row with the previous row value.

Syntax

LAG (scalar_expression [,offset] [,default])  
OVER ([partition_by_clause] order_by_clause)

Here,

Scalar_expression: 

Here, we have to define the name of the column or expression. The Lag() function performs calculations on this column. this argument is mandatory to define. Without this argument, the Lag() function can’t be executed.

Offset:

In this argument, we have to define the integer number. This number specified which number of the previous row value we have to compare.

default:

default is the value that will return when the offset value does not lie in the boundary of data. The default value is NULL when it is not defined.

partition_by_clause:

The partition by clause divides the result set into partitions, on which Lag() Function is applied. If we do not use partition by clause then, it will consider the whole result set as a single partition.

order_by_clause:

The order by clause defines the logical order of the row in each partition, on which the Lag() function is applied.

Example

1.  Without Partition by Clause

WITH SalesData As
(
SELECT Month, sum(NetSales) NetSales
FROM Sales
group by Month
)
select Month, NetSales, LAG(NetSales,1) OVER (order by Month) PreviousMonthSales
From SalesData

Output,

2. With the use of partition by clause

SELECT	s.Brand,
    s.Month,
    s.NetSales,
    LAG(s.NetSales,1) OVER(Partition by s.Brand order by s.Month)
From Sales as s

Output,

I hope this will help you to understand the concept of the LAG() function in SQL Server.

Thank you.

Also Check, Common Table Expression (CTE) In SQL Server.