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: