In the article SQL Server Lead function overview and examples, we explored Lead function for performing computational operations on data. This article gives an overview of the SQL Lag function and its comparison with the SQL Lead function.
Overview of SQL Lag function
We use a Lag() function to access previous rows data as per defined offset value. It is a window function available from SQL Server 2012 onwards. It works similar to a Lead function. In the lead function, we access subsequent rows, but in lag function, we access previous rows. It is a useful function in comparing the current row value from the previous row value.
Syntax of Lag function
|
1 2 |
LAG (scalar_expression [,offset] [,default]) OVER ( [ partition_by_clause ] order_by_clause ) |
It uses following arguments.
- Scalar_expression: We define a column name or expression in this argument. The lag function does calculations on this column. It is a mandatory argument, and we cannot execute the lag function without this
- Offset: We define an integer number in this argument. The lag function uses this argument forgo behind the number of rows (offset). The default value for this argument is one. It is an optional argument
- Default: Suppose we define an offset, value that does not lie in the boundary of the data. For example, we specified offset value 3 for the first row. A lag function cannot go three rows behind. It displays the default value if specified. If we do not specify any value for this, the lag function displays NULL in the output for out of range values
- PARTITION BY: It creates a logical boundary of data. Suppose we have an extensive data set and we require calculations on a smaller set of data, we can define partitions for it. For example, sales data for an organization might contain data for several years. We can create a partition quarterly and do the computation. It is as well an optional argument
- ORDER BY: We can sort data in ascending or descending order using ORDER by clause. By default, it uses ascending order to sort data
We will use data from the previous article for demonstration of SQL Server Lag function as well:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DECLARE @Employee TABLE ( EmpCode VARCHAR(10), EmpName VARCHAR(10), JoiningDate DATE ) INSERT INTO @Employee VALUES ('1', 'Rajendra', '1-Sep-2018') INSERT INTO @Employee VALUES ('2', 'Manoj', '1-Oct-2018') INSERT INTO @Employee VALUES ('3', 'Sonu', '10-Mar-2018') INSERT INTO @Employee VALUES ('4', 'Kashish', '25-Oct-2018') INSERT INTO @Employee VALUES ('5', 'Tim', '1-Dec-2018') INSERT INTO @Employee VALUES ('6', 'Akshita', '1-Nov-2018') GO SELECT * FROM @Employee; |
We have the following data in the Employee table:
Example 1: SQL Lag function without a default value
Execute the following query to use the Lag function on the JoiningDate column with offset one. We did not specify any default value in this query.
Execute the following query (we require to run the complete query along with defining a variable, its value):
|
1 2 3 4 |
SELECT *, Lag(JoiningDate, 1) OVER(. ORDER BY JoiningDate ASC) AS EndDate FROM @Employee; |
In the output, we can note the following:
- The first row shows NULL value for the EndDate column because it does not have any previous rows
- The second row contains previous row value in the EndDate column. It takes value from the previous row due to offset value 1
Example 2: SQL Lag function with a default value
In the previous example, we get NULL value as a default value. Let’s use a default end date in the lag function. This example also uses offset value 1 in the lag function:
|
1 2 3 4 |
SELECT *, Lag(JoiningDate, 1,'1999-09-01') OVER( ORDER BY JoiningDate ASC) AS EndDate FROM @Employee; |
In the output, we can see a default value instead of NULL in the first row:
We can use compatible data types in the default value column. If we use incompatible data types, we get the following error message:
Example 3: SQL Lag function with OFFSET value 2
Previously, we used default offset 1 in Lag function, and it takes value from the previous row. In the example, we use offset value 2. In the output, you can see we have a default value for row 1 and 2. In row 3, it takes value from row 1:
|
1 2 3 4 |
SELECT *, Lag(JoiningDate, 2,'1999-09-01') OVER( ORDER BY JoiningDate ASC) AS EndDate FROM @Employee; |
Example 4: SQL Lag function with PARTITION BY clause
As discussed earlier, we use the PARTITION BY clause to create a logical subset of data. Let’s use this PARTITION function on the ProductSales table. You can refer to the SQL Server Lead function to create this table:
In the following query, we use SQL Server Lag function and view the output:
|
1 2 3 4 5 6 7 |
SELECT [Year], [Quarter], Sales, LAG(Sales, 1, 0) OVER( ORDER BY [Year], [Quarter] ASC) AS [NextQuarterSales] FROM dbo.ProductSales; |
In the output, the lag function considers all rows as a single data set and applies Lag function:
In the ProductSales table, we have data for the years of 2017, 2018 and 2019. We want to use a lag function on a yearly basis. We use the PARTITION BY clause on the Year column and define the logical subset of data on a yearly basis. We use the Order by clause on year and quarter columns to sort data first on a yearly basis and then monthly:
|
1 2 3 4 5 6 7 |
SELECT [Year], [Quarter], Sales, LAG(Sales, 1, 0) OVER(PARTITION BY [Year] ORDER BY [Year], [Quarter] ASC) AS [NextQuarterSales] FROM dbo.ProductSales; |
In the following screenshot, we can see three partitions of data for 2017,2018 and 2019 year. The Lag function individually works on each partition and calculates the required data:
Conclusion
In this article, we learned the SQL Lag function and its usage to retrieve a value from previous rows. Here is the quick summary of the lag function:
- Lag function fetches the value from the previous rows based on the offset defined
- Offset one is the default offset value, and in this Lag, the function retrieves a value from the previous row
- PARTITION BY clause defines a logical boundary of data based on the specified condition
- The lag function uses default value NULL for out-of-range data
- We can use the Lag function with common table expression, stored procedures, and functions for computation purposes
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023