SQL Server LEAD Function

Summary: in this tutorial, you will learn how to use the SQL Server LEAD() function to access a row at a specific physical offset which follows the current row.

Overview of SQL Server LEAD() function

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

For example, by using the LEAD() function, from the current row, you can access data of the next row, or the row after the next row, and so on.

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

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

LEAD(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 following row based on a specified offset. The return value must evaluate to a single value and cannot be another window function.

 offset

offset is the number of rows forward from the current row from which to access data. The 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

The function returns default if offset goes beyond the scope of the partition. If not specified, it defaults to NULL.

 PARTITION BY clause

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

If you do not specify the PARTITION BY clause, the function treats the whole result set as a single partition.

 ORDER BY clause

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

SQL Server LEAD() function examples

Let’s create a new view named sales.vw_netsales_brands for the demonstration:

CREATE VIEW sales.vw_netsales_brands
AS
	SELECT 
		c.brand_name, 
		MONTH(o.order_date) month, 
		YEAR(o.order_date) year, 
		CONVERT(DEC(10, 0), SUM((i.list_price * i.quantity) * (1 - i.discount))) AS net_sales
	FROM sales.orders AS o
		INNER JOIN sales.order_items AS i ON i.order_id = o.order_id
		INNER JOIN production.products AS p ON p.product_id = i.product_id
		INNER JOIN production.brands AS c ON c.brand_id = p.brand_id
	GROUP BY c.brand_name, 
			MONTH(o.order_date), 
			YEAR(o.order_date);
Code language: SQL (Structured Query Language) (sql)

The following query returns 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 LEAD() function over a result set example

The following statement uses the LEAD() function to return the net sales of the current month and the next month in the year 2017:

WITH cte_netsales_2017 AS(
	SELECT 
		month, 
		SUM(net_sales) net_sales
	FROM 
		sales.vw_netsales_brands
	WHERE 
		year = 2017
	GROUP BY 
		month
)
SELECT 
	month,
	net_sales,
	LEAD(net_sales,1) OVER (
		ORDER BY month
	) next_month_sales
FROM 
	cte_netsales_2017;
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 LEAD() function to return the following month sales for each month.

By doing this, you can easily compare the sales of the current month with the next month.

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

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

SELECT 
	month,
	brand_name,
	net_sales,
	LEAD(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 LEAD() function returns the net sales of the following row.

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