Purpose
You can call this function to return the last value in an ordered value group. It is an analytic function. 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 sequence. Notice You cannot use analytic functions such as LAST_VALUE as expr. |
| {RESPECT IGNORE} NULLS | (Optional) Specifies whether to ignore NULL values. 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 about analytic functions, see Analytic functions. |
Return types
This function returns the same value type as the expr parameter or returns NULL.
Examples
Assume that you have created the table emp_msg. 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