All Products
Search
Document Center

LEAD

Last Updated: Jun 18, 2021

LEAD is an analytic function. It provides access to multiple rows of a table without a self join. Given a series of rows that are returned from a query and a cursor position, LEAD provides access to a row at a physical offset beyond this position.

Syntax

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

Parameters

Parameter

Description

OVER

Uses the OVER clause to define a window for calculation.

offset

The offset of value_expr. This parameter is optional.

default

If you do not specify the default value, the default value is null. If the default value is not explicitly specified in LEAD, the return value is NULL.

{RESPECT | IGNORE} NULLS

Specifies whether to ignore NULL values. The default value is RESPECT NULLS and indicates that NULL values are taken into consideration.

value_expr

The field to be compared. You cannot use the LEAD function or other analytic functions to nest value_expr.

Notice

The LEAD function must be followed by order_by_clause. query_partition_clause is optional.

Return type

The returned data type is not limited.

Examples

To create the emp_msg table and insert data into columns, execute the following statements:

CREATE TABLE emp_msg(deptno INT, ename VARCHAR(30),sal INT);
INSERT INTO emp_msg VALUES(20,'ADAMS',1400);
INSERT INTO emp_msg VALUES(30,'ALLEN',1900);
INSERT INTO emp_msg VALUES(30,'BLAKE',3135);
INSERT INTO emp_msg VALUES(10,'CLARK',2750);
INSERT INTO emp_msg VALUES(20,'FORD',3300);
INSERT INTO emp_msg VALUES(30,'JAMES',1250);
INSERT INTO emp_msg VALUES(20,'JONES',3275);
INSERT INTO emp_msg VALUES(10,'KING',5300);
INSERT INTO emp_msg VALUES(30,'MARTIN',1550);
INSERT INTO emp_msg VALUES(10,'MILLER',1600);
INSERT INTO emp_msg VALUES(20,'SCOTT',3300);
INSERT INTO emp_msg VALUES(20,'SWITH',1100);
INSERT INTO emp_msg VALUES(30,'TURNER',1800);
INSERT INTO emp_msg VALUES(30,'WARD',1550);

Query the emp_msg table. Replace the last five values with Jane. Start to append the values that are sorted by the ename field in ascending order from the last but five value.

SELECT  deptno, ename, sal, LEAD(ename,5,'Jane') OVER (ORDER BY ename) AS new_ename 
FROM emp_msg;

The following query result is returned:

+--------+--------+------+-----------+
| DEPTNO | ENAME  | SAL  | NEW_ENAME |
+--------+--------+------+-----------+
|     20 | ADAMS  | 1400 | JAMES     |
|     30 | ALLEN  | 1900 | JONES     |
|     30 | BLAKE  | 3135 | KING      |
|     10 | CLARK  | 2750 | MARTIN    |
|     20 | FORD   | 3300 | MILLER    |
|     30 | JAMES  | 1250 | SCOTT     |
|     20 | JONES  | 3275 | SWITH     |
|     10 | KING   | 5300 | TURNER    |
|     30 | MARTIN | 1550 | WARD      |
|     10 | MILLER | 1600 | Jane      |
|     20 | SCOTT  | 3300 | Jane      |
|     20 | SWITH  | 1100 | Jane      |
|     30 | TURNER | 1800 | Jane      |
|     30 | WARD   | 1550 | Jane      |
+--------+--------+------+-----------+