LEAD

Purpose

This function computes the result of expr on the row that is precisely offset rows following current row in the partition.

Usage Notes

• LEAD is only available as an analytic function (i.e., in combination with an over_clause). For more information, refer to Analytic Functions section.
• The over_clause has to contain an order_clause and must not contain a window_frame_clause.
• If the order_clause does not define an unique sort order, the result is non-deterministic.
• The offset parameter has to be a numeric value greater than or equal to 0. Without offset, Exasol uses the default value 1.
• If there are less than offset number of rows following the current row, LEAD returns the default value for the row. Without specified default, Exasol uses NULL.
• If the function contains IGNORE NULLS, Exasol skips rows with NULL during the search for the leading row in the partition. It does not skip any rows with RESPECT NULLS. If the function contains neither IGNORE NULLS nor RESPECT NULLS the default is RESPECT NULLS. The use of IGNORE NULLS is computationally more expensive than RESPECT NULLS.
• To access preceding rows you can use the function LAG.

Example

Analytic Function

SELECT 
    id, department, hire_date, 
    LEAD(id, 1) OVER (PARTITION BY department ORDER BY hire_date) LEAD 
FROM employee_table ORDER BY department, hire_date;

Result