SQL Server LAG() Function

This blog is going to illustrate the functionality of SQL LAG() and how to use it.

The SQL LAG() function gives the previous row data of the offset value. This functionality has been introduced on the SQL Server 2012.

The following is the sample table. There are three columns in table SampleID, Year, and Profit. Let’s see how to get the previous row value. 

SELECT * FROM SampleTable

The following is an example of the SQL query LAG() function. The query explains how to get the previous year’s profit.

SELECT SampleID, Year, Profit, 

LAG(Profit, 1) OVER (ORDER BY SampleID ASC) AS PreviousProfit 

FROM SampleTable

The following table shows the output of the query above. You can see the new column called PreviousProfit here. The first row of the PreviousProfit column is null because there is no previous value.

Default value

When using the LAG() function, the first result will be null. But it can be assigned a default value. In the following query, the default value is added as 100000

SELECT SampleID, Year, Profit, 

LAG(Profit, 1, 100000) OVER (ORDER BY SampleID ASC) AS PreviousProfit 

FROM SampleTable

The following image is the output of the default value for the LAG() function, you can see that the first-row value of the PreviousProfit column is 100000

OFFSET value 2

Also, OFFSET value 2 can be used to get the first-row value in the third row instead of the second row.

SELECT SampleID, Year, Profit, 

LAG(Profit, 2, 100000) OVER (ORDER BY SampleID ASC) AS PreviousProfit 

FROM SampleTable

PARTITION BY

You can use the LAG() function with the PARTITION BY clause.

SELECT SampleID, [Quarter], [Year], Profit, 

LAG(Profit, 1, 100000) OVER (PARTITION BY [Year] ORDER BY [Year] ASC) AS PreviousProfit 

FROM SampleTable

In the following figure, you can see how the LAG() function returns results using the PARTITION BY clause. LAG() returns the result based on the partition. Each partition is assigned a default value (100000).

I hope this will help you. keep Coding