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 |
+--------+--------+------+-----------+