Summary: in this tutorial, you will learn how to access the following row from the current row by using the Oracle
Introduction to Oracle
The following shows the syntax of the
LEAD(expression [, offset ] [, default ]) OVER ( [ query_partition_clause ] order_by_clause )
In this syntax:
is a scalar expression evaluated against the value of the row specified by
offset. It must return a single value.
is the number of rows forwarding from the current row from which to get the value. The default is 1.
If the offset goes beyond the scope of the partition, the function returns the
default. If you skip default, then the function will return
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 clause specifies the order the rows in each partition to which the
LEAD() function is applied.
LEAD() function is very useful for calculating the difference between the values of current and subsequent rows.
LEAD() function examples
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);
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;
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;
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;
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.