All Products
Search
Document Center

LAST_VALUE

Last Updated: Jun 18, 2021

The LAST_VALUE function is an analytic function. It returns the last value in a set of the ordered values. If the last value in the set is NULL, the function returns NULL unless you specify IGNORE NULLS. This configuration is useful for data densification.

Syntax

LAST_VALUE {(expr) [RESPECT|IGNORE NULLS] | (expr [RESPECT|IGNORE NULLS])}
OVER (analytic_clause)

Parameters

Parameter

Description

OVER

Uses the OVER clause to define a window for calculation.

expr

You cannot use LAST_VALUE or other analytic functions for expr to nest analytic functions.

FROM { FIRST | LAST }

Specifies whether the calculation starts from the first or last row of the window. The default value is FROM FIRST.

{RESPECT | IGNORE} NULLS

Specifies whether to ignore NULL values. The default value is RESPECT NULLS and indicates that NULL values are taken into consideration. If you specify IGNORE NULLS, LAST_VALUE returns the last non-null value in the set. If all the values are NULL, NULL is returned.

Return type

The data type of the return value is not limited.

Examples

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

CREATE TABLE emp_msg(deptno INT, ename varchar(30),sal INT, MGR varchar(30));
INSERT INTO emp_msg VALUES(10,'CLARK', 2750, 7839);       
INSERT INTO emp_msg VALUES(10,'KING', 5300, NULL);       
INSERT INTO emp_msg VALUES(10,'MILLER', 1600, 7782);        
INSERT INTO emp_msg VALUES(20,'ADAMS', 1400, 7788);       
INSERT INTO emp_msg VALUES(20,'FORD', 3300, 7566);      
INSERT INTO emp_msg VALUES(20,'JONES', 3275, 7839);      
INSERT INTO emp_msg VALUES(20,'SCOTT', 3300, 7566);    
INSERT INTO emp_msg VALUES(20,'SMITH', 1100, 7902);   
INSERT INTO emp_msg VALUES(30,'ALLEN', 1900, 7698); 
INSERT INTO emp_msg VALUES(30,'BLAKE', 3150, 7839); 
INSERT INTO emp_msg VALUES(30,'JAMES', 1250, 7698); 
INSERT INTO emp_msg VALUES(30,'MARTIN', 1550, 7698);
INSERT INTO emp_msg VALUES(30,'TURNER', 1800, 7698);
INSERT INTO emp_msg VALUES(30,'WARD', 1550, 7698);

To query the lowest and the last non-null MGR value in the sal column in the emp_msg table and use the queried value as the last_MGR column, execute the following statement:

SELECT deptno , ename , sal , MGR ,
LAST_VALUE ( MGR ) IGNORE NULLS OVER (ORDER BY sal DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS last_MGR 
FROM emp_msg ORDER BY deptno , ename ;

The following query result is returned:

+--------+--------+------+------+-----------+
| DEPTNO | ENAME  | SAL  | MGR  | FIRST_MGR |
+--------+--------+------+------+-----------+
|     10 | CLARK  | 2750 | 7839 | 7839      |
|     10 | KING   | 5300 | NULL | NULL      |
|     10 | MILLER | 1600 | 7782 | 7782      |
|     20 | ADAMS  | 1400 | 7788 | 7788      |
|     20 | FORD   | 3300 | 7566 | 7566      |
|     20 | JONES  | 3275 | 7839 | 7839      |
|     20 | SCOTT  | 3300 | 7566 | 7566      |
|     20 | SMITH  | 1100 | 7902 | 7902      |
|     30 | ALLEN  | 1900 | 7698 | 7698      |
|     30 | BLAKE  | 3150 | 7839 | 7839      |
|     30 | JAMES  | 1250 | 7698 | 7698      |
|     30 | MARTIN | 1550 | 7698 | 7698      |
|     30 | TURNER | 1800 | 7698 | 7698      |
|     30 | WARD   | 1550 | 7698 | 7698      |
+--------+--------+------+------+-----------+