SQL LAG Function

The SQL LAG is one of the Analytic Function, which is exactly opposite to LEAD. This SQL Server lag function allows you to access the data from a previous row without using any SELF JOIN. The basic syntax of the LAG in SQL Server is as shown below:

SELECT LAG([Scalar Expression], [Offset], [Default]) 
                  OVER (
                         PARTITION_BY_Clause 
                         ORDER_BY_Clause
                        )
FROM [Source]
  • Scalar Expression: It can be a Column, expression, or Subquery that returns single value in SQL Server.
  • Offset: Please define the number of rows you want to move backward. For example, If it is 3, then it will select the 3rd previous row (-3 row) as a result — Use Column, Subquery, or any expression that returns a single value.
  • Default: You can define the default value. If you ignore, SQL Lag will write NULL values
  • Order_By_Clause: This is used to sort the Partitioned data. Please refer to Order By Clause in SQL Server.
  • Partition_By_Clause: It separates the Selected records into partitions. 
    • If you specified the Partition By Clause, the SQL Server LAG function starts picking the previous rows in each partition.
    • If you haven’t specified the Partition By, LAG Function will consider all rows as a single partition. 

For this LAG demo, we are going to use the below-shown data

SQL LAG 1

SQL LAG without Partition By Clause

What happens when we ignore the Partition By Clause in the LAG function in the SQL server?

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,LAG([Sales]) OVER (ORDER BY [YearlyIncome]) AS CompareSale 
  FROM [Employee]

As you can see, the lag function returned NULL as output for the 1st row. It’s because there is no previous row for that record (it is the first row indeed)

SQL LAG Function 1

This statement sort the Employee records in Ascending order based on their Yearly Income

ORDER BY [YearlyIncome]

Next, the LAG function will return the previous value of the row (row before) as the output. If there are no rows to return, the SQL Server Lag function will return a NULL value because we haven’t set any default value

LAG([Sales]) OVER (ORDER BY [YearlyIncome]) AS CompareSale

Let me define the Offset value =1, and the default value = 0

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,LAG([Sales], 1, 0) OVER (ORDER BY [YearlyIncome]) AS CompareSale 
FROM [Employee]
SQL LAG Function 2

Now you can see that, the lag function is replacing NULL value with 0. Please refer to LEAD and SELF JOIN articles.

SQL LAG with Partition By Example

How to write the previous values from the partitioned records?. The following SQL Server Lag Query partition the data by Occupation using their yearly Income. And then, it returns previous Sales values in each partition.

-- Sql Server Lag Example
SELECT [FirstName] + ' ' + [LastName] AS [Full Name]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,LAG([Sales], 1, 0) OVER (
   PARTITION BY [Occupation]
   ORDER BY [YearlyIncome]
 ) AS SaleCompare 
FROM [Employee]

If you see, for the 1st, 5th, 9th, 13th row, SQL Lag function assigned 0 because there is no previous records within that partition.

SQL LAG Function 3

In the below statement, we used LAG Function with Partition by clause. It will select the previous rows with the individual partition.

LAG([Sales], 1, 0) OVER (
			  PARTITION BY [Occupation]
			  ORDER BY [YearlyIncome]
			) AS SaleCompare

SQL LAG with Offset Value

Let us see what happen when we change the LAG Function offset value from 1 to 2 and the default value to 100.

-- Sql Server Lag Example
SELECT [FirstName] + ' ' + [LastName] AS [Full Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,LAG([Sales], 2, 100) OVER (
   PARTITION BY [Occupation]
   ORDER BY [YearlyIncome]
          ) AS SaleCompare  
FROM [Employee]

The lag function is selecting the -2nd row (2 rows ahead), rather than choosing the previous row. If you modify the value to 3, then LAG function will select the last third row (-3rd row) by jumping the last two rows, etc.

SQL LAG Function 4

Comments are closed.