LEAD function in Oracle

LEAD is one of the vital Analytic functions of Oracle. It is used to query more than one row in a table at a time. With the use of LEAD function there is no need to join the table to itself. The result is the values from the next row in the table. The LEAD function is supported in the various versions of the Oracle/PLSQL, including, Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i and Oracle 8i.

Syntax:

LEAD ( expression, offset, default )
OVER ( [ query_partition_clause ] order_by_clause )

Parameters:
expression: It is used to specify the expressions containing built-in functions but no analytic functions.
offset: It is an optional parameter which is used to specify the physical offset from the current row in the table with a default value of 1.
default: It is also an optional parameter which is used to specify a value that is returned if the offset goes out of the bounds of the table with a default value of NULL.
query_partition_clause: It is also an optional parameter which is used to partition the results into groups.
order_by_clause: It is also an optional parameter which is used to order the data within each partition.

Example 1: Without using query_partition_clause.

Students Table:

ID	MARKS	EXTRA_MARKS
1	80	10
2	100	20
3	95	15
4	75	10
5	85	15

Query:

SELECT id, marks,
LEAD (marks,1) OVER (ORDER BY marks) AS next_marks
FROM students;

Output:

ID	MARKS	NEXT_MARKS
4	75	80
1	80	85
5	85	95
3	95	100
2	100	NULL

Explanation:
Here, the LEAD function will sort all of the ‘marks’ in ascending order in the ‘students’ table and since we used an offset of 1, it will then return the next marks. The last record in the result set has a value of NULL for the ‘next_marks’ as there is no higher ‘marks’ value.

Example 2: Using query_partition_clause.
Students Table:

ID	MARKS	EXTRA_MARKS
1	80	10
2	100	20
3	95	15
4	75	10
5	85	15

Query:

SELECT extra_marks, marks,
LEAD (marks,1) OVER (PARTITION BY extra_marks ORDER BY marks) AS next_marks
FROM students;

Output:

EXTRA_MARKS	MARKS	NEXT_MARKS
10	        75	80
10	        80	NULL
15	        85	95
15	        95	NULL
20	        100	NULL

Explanation:
Here, the LEAD function will sort all of the ‘marks’ in ascending order after the partition of the results by ‘extra_marks’ in the ‘students’ table and since we used an offset of 1, it will then return the next marks. The LEAD function will restart its calculations, whenever a new ‘extra_marks’ is encountered. The 2nd record in the result set is the last record for the partition where ‘extra_marks’ is 10 and thus has a value of NULL for the ‘next_marks’ as there is no higher ‘marks’ value in this partition. This is also true for the 4th record which is the last record for the partition where the ‘extra_marks’ is 15 and the 5th record which is the last record for the partition where the ‘extra_marks’ is 20.