Syntax
LEAD { ( value_expr [, offset [, default]]) [ { RESPECT IGNORE } NULLS ] ( value_expr [ { RESPECT IGNORE } NULLS ] [, offset [, default]] )} OVER ([ query_partition_clause ] order_by_clause)
Purpose
You can call this function to retrieve the value of the Nth leading row of the current row in the same field. A self join on the same table achieves the same result, but the LEAD() function is more efficient.
Parameters
The following table describes the parameters of the function.
| Parameter | Description |
|---|---|
value_expr |
The fields to be compared. |
offset |
The offset of value_expr. |
default |
The default return value. The default value NULL indicates that if you do not explicitly specify a default value, the return value is NULL. |
[ { RESPECT IGNORE } NULLS ] |
Specifies whether to include NULLs in the result. The default value RESPECT NULLS specifies to include NULLs in the calculation. |
order_by_clause |
Specifies that the data must be sorted on a column to determine the ordinal number of rows. |
query_partition_clause |
Specifies the queried partition. If you do not specify the argument, all data rows are queried. |
Examples
obclient> CREATE TABLE EXPLOYEES(LAST_NAME CHAR(10), SALARY DECIMAL, JOB_ID CHAR(32));
Query OK, 0 rows affected (0.08 sec)
obclient> INSERT INTO EXPLOYEES VALUES('JIM', 2000, 'CLEANER');
Query OK, 1 row affected (0.11 sec)
obclient> INSERT INTO EXPLOYEES VALUES('MIKE', 12000, 'ENGINEERING');
Query OK, 1 row affected (0.00 sec)
obclient> INSERT INTO EXPLOYEES VALUES('LILY', 13000, 'ENGINEERING');
Query OK, 1 row affected (0.00 sec)
obclient> INSERT INTO EXPLOYEES VALUES('TOM', 11000, 'ENGINEERING');
Query OK, 1 row affected (0.01 sec)
obclient> SELECT LAST_NAME, LEAD(SALARY) OVER(ORDER BY SALARY) LEAD, LAG(SALARY) OVER(ORDER BY SALARY) LAG FROM EXPLOYEES;
+-----------+-------+-------+
LAST_NAME LEAD LAG
+-----------+-------+-------+
JIM 11000 NULL
TOM 12000 2000
MIKE 13000 11000
LILY NULL 12000
+-----------+-------+-------+
4 rows in set (0.00 sec)