SQL Server LAG Function

Summary: in this tutorial, you will learn how to use the LAG() function to access a row at a specific physical offset which comes before the current row.

Overview of SQL Server LAG() function

SQL Server LAG() is a window function that provides access to a row at a specified physical offset which comes before the current row.

In other words, by using the LAG() function, from the current row, you can access data of the previous row, or the row before the previous row, and so on.

The LAG() function can be very useful for comparing the value of the current row with the value of the previous row.

The following shows the syntax of the LAG() function:

LAG(return_value ,offset [,default]) 
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)
Code language: SQL (Structured Query Language) (sql)

In this syntax:

 return_value

The return value of the previous row based on a specified offset. The return value must evaluate to a single value and cannot be another window function.

 offset

The number of rows back from the current row from which to access data.  offset can be an expression, subquery, or column that evaluates to a positive integer.

The default value of offset is 1 if you don’t specify it explicitly.

 default

default is the value to be returned if offset goes beyond the scope of the partition. It defaults to NULL if it is not specified.

 PARTITION BY clause

The PARTITION BY clause distributes rows of the result set into partitions to which the LAG() function is applied.

If you omit the PARTITION BY clause, the function will treat the whole result set as a single partition.

 ORDER BY clause

The ORDER BY clause specifies the logical order of the rows in each partition to which the LAG() function is applied.

SQL Server LAG() function examples

We will reuse the view sales.vw_netsales_brands created in the LEAD() function tutorial for the demonstration.

The following query shows the data from the sales.vw_netsales_brands view:

SELECT 
	*
FROM 
	sales.vw_netsales_brands
ORDER BY 
	year, 
	month, 
	brand_name, 
	net_sales;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

A) Using SQL Server LAG() function over a result set example

This example uses the LAG() function to return the net sales of the current month and the previous month in the year 2018:

WITH cte_netsales_2018 AS(
	SELECT 
		month, 
		SUM(net_sales) net_sales
	FROM 
		sales.vw_netsales_brands
	WHERE 
		year = 2018
	GROUP BY 
		month
)
SELECT 
	month,
	net_sales,
	LAG(net_sales,1) OVER (
		ORDER BY month
	) previous_month_sales
FROM 
	cte_netsales_2018;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

In this example:

• First, the CTE returns net sales aggregated by month.
• Then, the outer query uses the LAG() function to return sales of the previous month.

B) Using SQL Server LAG() function over partitions example

The following statement uses the LAG() function to compare the sales of the current month with the previous month of each brand in the year 2018:

SELECT 
	month,
	brand_name,
	net_sales,
	LAG(net_sales,1) OVER (
		PARTITION BY brand_name
		ORDER BY month
	) next_month_sales
FROM 
	sales.vw_netsales_brands
WHERE
	year = 2018;
Code language: SQL (Structured Query Language) (sql)

This picture shows the output:

In this example:

• The PARTITION BY clause divided rows into partitions by brand name.
• For each partition (or brand name), the ORDER BY clause sorts the rows by month.
• For each row in each partition, the LAG() function returns the net sales of the previous row.

To compare the sales of the current month with the previous month of net sales by brand in 2018, you use the following query:

WITH cte_sales AS (
	SELECT 
		month,
		brand_name,
		net_sales,
		LAG(net_sales,1) OVER (
			PARTITION BY brand_name
			ORDER BY month
		) previous_sales
	FROM 
		sales.vw_netsales_brands
	WHERE
		year = 2018
)
SELECT 
	month, 
	brand_name,
	net_sales, 
	previous_sales,
	FORMAT(
		(net_sales - previous_sales)  / previous_sales,
		'P'
	) vs_previous_month
FROM
	cte_sales;
Code language: SQL (Structured Query Language) (sql)

The output is as follows:

In this tutorial, you have learned how to use the SQL Server LAG() function to access a row at a specific physical offset which follows the current row.