LEAD

Purpose

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

Syntax

lead::=

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
ID DEPARTMENT HIRE_DATE LEAD
2005 ACCOUNTS 2013-01-01 2003
2003 ACCOUNTS 2015-07-01 2002
2002 ACCOUNTS 2017-01-01 2004
2004 ACCOUNTS 2017-01-01 2001
2001 ACCOUNTS 2018-07-01 NULL
1003 HR 2014-01-01 1002
1002 HR 2016-01-01 1004
1004 HR 2016-01-01 1001
1001 HR 2018-01-01 NULL