Purpose
This function is an analytic function that returns the last value in an ordered set of values. 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 | Specifies the expression. Note You cannot nest an analytic function, such as LAST_VALUE, in the expr parameter. |
| {RESPECT | IGNORE} NULLS | Specifies whether to ignore NULL values. This is an optional parameter. The default value is RESPECT NULLS.
|
| OVER | Defines the window for calculation using the OVER clause. For more information, see Analytic Function Description. |
Return type
The return type is the same as the data type of expr or NULL.
Examples
Assume that the emp_msg table has been created. Sort the data in the emp_msg table by the sal column in ascending order and return the last non-NULL value of the MGR column as 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