Purpose
LAST_VALUE() is an analytic function. It returns the last value in an ordered value group. If the last value in the set is NULL, the function returns NULL unless you specify IGNORE NULLS, which is useful for data densification.
Syntax
LAST_VALUE { (expr) [{RESPECT | IGNORE} NULLS ]
| (expr [{RESPECT | IGNORE} NULLS ])
}
OVER (analytic_clause)
Parameters
Parameter |
Description |
|---|---|
| expr | The sequence. Notice: You cannot nest expr in analytic functions such as LAST_VALUE(). |
| {RESPECT | IGNORE} NULLS | Optional. Specifies whether to ignore NULLs. Default value: RESPECT NULLS.
|
| OVER | You can use the OVER clause to define a window over the data on which the function operates. For more information, see Analytic functions. |
Return type
The return type is the same as the data type of expr, or this function returns NULL.
Examples
The table emp_msg has been created. Sort data records in the emp_msg table by the sal column in ascending order, and fill the last non-NULL value in the mgr column to the last_mgr column.
obclient> SELECT * FROM emp_msg;
+--------+--------+------+------+
| DEPTNO | ENAME | SAL | MGR |
+--------+--------+------+------+
| 10 | CLARK | 2750 | 7839 |
| 10 | KING | 5300 | NULL |
| 10 | MILLER | 1600 | 7782 |
| 20 | ADAMS | 1400 | 7788 |
| 20 | FORD | 3300 | 7566 |
| 20 | JONES | 3275 | 7839 |
| 20 | SCOTT | 3300 | 7566 |
| 20 | SMITH | 1100 | 7902 |
| 30 | ALLEN | 1900 | 7698 |
| 30 | BLAKE | 3150 | 7839 |
| 30 | JAMES | 1250 | 7698 |
| 30 | MARTIN | 1550 | 7698 |
| 30 | TURNER | 1800 | 7698 |
| 30 | WARD | 1550 | 7698 |
| 30 | SCLARK | 1750 | 7839 |
+--------+--------+------+------+
15 rows in set
obclient> SELECT deptno,ename,sal,mgr,
LAST_VALUE (mgr) IGNORE NULLS OVER (ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS last_mgr
FROM emp_msg;
+--------+--------+------+------+----------+
| DEPTNO | ENAME | SAL | MGR | LAST_MGR |
+--------+--------+------+------+----------+
| 20 | SMITH | 1100 | 7902 | 7566 |
| 30 | JAMES | 1250 | 7698 | 7566 |
| 20 | ADAMS | 1400 | 7788 | 7566 |
| 30 | MARTIN | 1550 | 7698 | 7566 |
| 30 | WARD | 1550 | 7698 | 7566 |
| 10 | MILLER | 1600 | 7782 | 7566 |
| 30 | SCLARK | 1750 | 7839 | 7566 |
| 30 | TURNER | 1800 | 7698 | 7566 |
| 30 | ALLEN | 1900 | 7698 | 7566 |
| 10 | CLARK | 2750 | 7839 | 7566 |
| 30 | BLAKE | 3150 | 7839 | 7566 |
| 20 | JONES | 3275 | 7839 | 7566 |
| 20 | FORD | 3300 | 7566 | 7566 |
| 20 | SCOTT | 3300 | 7566 | 7566 |
| 10 | KING | 5300 | NULL | 7566 |
+--------+--------+------+------+----------+
15 rows in set