Rajendra Gupta

SQL Lag function overview and examples

October 15, 2019 by

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

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:

We have the following data in the Employee table:

Sample data

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):

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

Lag function without a default value

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:

In the output, we can see a default value instead of NULL in the first row:

Lag function with a default value

We can use compatible data types in the default value column. If we use incompatible data types, we get the following error message:

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:

Lag function with OFFSET value 2

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:

SQL Server Lag function with PARTITION BY clause

In the following query, we use SQL Server Lag function and view the output:

In the output, the lag function considers all rows as a single data set and applies Lag function:

SQL Server Lag function with PARTITION BY clause

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:

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:

Lag function with partition

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
Rajendra Gupta
Latest posts by Rajendra Gupta (see all)

Related posts:

  1. SQL Order by Clause overview and examples
  2. An overview of the CONCAT function in SQL with examples
  3. Overview of the SQL ROW_NUMBER function
  4. SQL Server Lead function overview and examples
  5. An overview of the STRING_AGG function in SQL
168 Views