Oracle LEAD

Summary: in this tutorial, you will learn how to access the following row from the current row by using the Oracle LEAD() function.

Introduction to Oracle LEAD() function

Oracle LEAD() is an analytic function that allows you to access the following row from the current row without using a self-join.

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

LEAD(expression [, offset ] [, default ])
OVER (
	[ query_partition_clause ] 
	order_by_clause
)Code language: SQL (Structured Query Language) (sql)

In this syntax:

expression

is a scalar expression evaluated against the value of the row specified by offset. It must return a single value.

offset

is the number of rows forwarding from the current row from which to get the value. The default is 1.

default

If the offset goes beyond the scope of the partition, the function returns the default. If you skip the default, then the function will return NULL.

query_partition_clause

The query_partition_clause clause divided the rows into partitions to which the LEAD() function is applied. By default, the function treats the whole result set as a single partition.

order_by_clause

The order_by_clause clause specifies the order of the rows in each partition to which the LEAD() function is applied.

The LEAD() function is very useful for calculating the difference between the values of current and subsequent rows.

Oracle LEAD() function examples

We will create a view named salesman_performance that returns the sales of the salesman by year based on the orders and order_items tables from the sample database:

CREATE VIEW salesman_performance (
	salesman_id, 
	year, 
	sales
) AS
SELECT 
	salesman_id, 
	EXTRACT(YEAR FROM order_date), 
	SUM(quantity*unit_price)
FROM 
	orders
INNER JOIN order_items USING (order_id)
WHERE 
	salesman_id IS NOT NULL
GROUP BY 
	salesman_id, 
	EXTRACT(YEAR FROM order_date);
Code language: SQL (Structured Query Language) (sql)

The following statement returns the data through the salesman_performance view :

SELECT 
   salesman_id,
   year, 
   sales
FROM 
   salesman_performance
ORDER BY
   salesman_id, 
   year, 
   sales;Code language: SQL (Structured Query Language) (sql)

Here is the partial output:

A) Using Oracle LEAD() function over the result set example

The following query uses the LEAD() function to return sales of the following year of the salesman id 55:

SELECT 
	salesman_id,
	year, 
	sales,
	LEAD(sales) OVER (
		ORDER BY year
	) following_year_sales
FROM 
	salesman_performance
WHERE
	salesman_id = 55;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

The last row returned NULL for the following_year_sales column because the offset went beyond the scope of the result set.

B) Using Oracle LEAD() function over partitions example

The following statement uses the LEAD() function to return sales of the following year for every salesman:

SELECT 
	salesman_id,
	year, 
	sales,
	LEAD(sales) OVER (
		PARTITION BY SALESMAN_ID
		ORDER BY year
	) following_year_sales
FROM 
	salesman_performance;
Code language: SQL (Structured Query Language) (sql)

The following picture displays the partial output:

In this tutorial, you have learned how to use the Oracle LEAD() function to access data of the following row from the current row in the partition without using a self-join.