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 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 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:

Oracle LEAD Function - Sample View

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:

Oracle LEAD Function Example

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:

Oracle LEAD Function Over Partition Example

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.

Was this tutorial helpful?