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.