LEAD

Declaration

LEAD { ( value_expr [, offset [, default]]) [ { RESPECT | IGNORE } NULLS ] | ( value_expr [ { RESPECT | IGNORE } NULLS ] [, offset [, default]] )} OVER ([ query_partition_clause ] order_by_clause)

Description

The LEAD function allows you to retrieve data from the Nth row after the current row in the same column within a single query. While this can be achieved using self-joins on the same table, the LEAD() window function is more efficient.

Parameters

The parameters are described in the table below.

Parameter	Description
value_expr	The column to compare.
offset	The offset of value_expr.
default	The default value. The default value is NULL, which indicates that the return value is NULL if no explicit default value is specified.
[ { RESPECT | IGNORE } NULLS ]	Specifies whether to consider NULL values. The default value is RESPECT NULLS, which indicates to consider NULL values.
order_by_clause	Indicates that the data should be sorted by a column to determine the rows before and after.
query_partition_clause	Indicates the query partition. If not specified, the global data is used.

Examples

CREATE TABLE EXPLOYEES(LAST_NAME CHAR(10), SALARY DECIMAL, JOB_ID CHAR(32));
Query OK, 0 rows aaffected (0.036 sec)

INSERT INTO EXPLOYEES VALUES('JIM', 2000, 'CLEANER');
Query OK, 1 row affected (0.001 sec)

INSERT INTO EXPLOYEES VALUES('MIKE', 12000, 'ENGINEERING');
Query OK, 1 row affected (0.001 sec)

INSERT INTO EXPLOYEES VALUES('LILY', 13000, 'ENGINEERING');
Query OK, 1 row affected (0.001 sec)

INSERT INTO EXPLOYEES VALUES('TOM', 11000, 'ENGINEERING');
Query OK, 1 row affected (0.001 sec)

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.002 sec)