Obtains the calculated result of the last row of data in the window to which the current row belongs.
Limits
Before you use window functions, take note of the following limits:
Window functions are supported only in
SELECTstatements.A window function cannot contain nested window functions or nested aggregate functions.
You cannot use window functions together with aggregate functions of the same level.
Syntax
last_value(<expr>[, <ignore_nulls>]) over([partition_clause] [orderby_clause] [frame_clause])Description
Obtains the calculated result of the last row of data in a window by using the expression that is specified by expr.
The rows of data that can be calculated in a window vary based on whether order by is specified. For more information, see Examples.
Parameters
expr: required. The expression that is used to calculate the returned result.
ignore_nulls: optional. A value of the BOOLEAN type. This parameter specifies whether to ignore null values. Default value: false. If this parameter is set to true, a non-null value of expr that corresponds to the last row of a window is returned.
partition_clause, orderby_clause, and frame_clause: For more information about these parameters, see windowing_definition.
Return value
A value of the same data type as expr is returned.
Sample data
This section provides sample source data and examples for you to understand how to use the functions. Create a table named emp and insert the sample data into the table. Sample statement:
create table if not exists emp
(empno bigint,
ename string,
job string,
mgr bigint,
hiredate datetime,
sal bigint,
comm bigint,
deptno bigint);
tunnel upload emp.txt emp; -- Replace emp.txt with the actual path (path and name) to which you upload the data file.The emp.txt file contains the following sample data:
7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10Examples
Group all employees based on the deptno column and return the last row of data in each group. Sample statement:
If order by is not specified, the rows from the first row to the last row belong to the current window. The value of the last row in the current window is returned.
select deptno, ename, sal, last_value(sal) over (partition by deptno) as last_value from emp;The following result is returned:
+------------+------------+------------+-------------+ | deptno | ename | sal | last_value | +------------+------------+------------+-------------+ | 10 | TEBAGE | 1300 | 2450 | | 10 | CLARK | 2450 | 2450 | | 10 | KING | 5000 | 2450 | | 10 | MILLER | 1300 | 2450 | | 10 | JACCKA | 5000 | 2450 | | 10 | WELAN | 2450 | 2450 | -- This row is the last row of the current window. | 20 | FORD | 3000 | 2975 | | 20 | SCOTT | 3000 | 2975 | | 20 | SMITH | 800 | 2975 | | 20 | ADAMS | 1100 | 2975 | | 20 | JONES | 2975 | 2975 | -- This row is the last row of the current window. | 30 | TURNER | 1500 | 2850 | | 30 | JAMES | 950 | 2850 | | 30 | ALLEN | 1600 | 2850 | | 30 | WARD | 1250 | 2850 | | 30 | MARTIN | 1250 | 2850 | | 30 | BLAKE | 2850 | 2850 | -- This row is the last row of the current window. +------------+------------+------------+-------------+If order by is specified, the rows from the first row to the current row belong to the current window. The value of the current row in the current window is returned.
select deptno, ename, sal, last_value(sal) over (partition by deptno order by sal desc) as last_value from emp;The following result is returned:
+------------+------------+------------+-------------+ | deptno | ename | sal | last_value | +------------+------------+------------+-------------+ | 10 | JACCKA | 5000 | 5000 | -- This row is the current row of the current window. | 10 | KING | 5000 | 5000 | -- This row is the current row of the current window. | 10 | CLARK | 2450 | 2450 | -- This row is the current row of the current window. | 10 | WELAN | 2450 | 2450 | -- This row is the current row of the current window. | 10 | TEBAGE | 1300 | 1300 | -- This row is the current row of the current window. | 10 | MILLER | 1300 | 1300 | -- This row is the current row of the current window. | 20 | SCOTT | 3000 | 3000 | -- This row is the current row of the current window. | 20 | FORD | 3000 | 3000 | -- This row is the current row of the current window. | 20 | JONES | 2975 | 2975 | -- This row is the current row of the current window. | 20 | ADAMS | 1100 | 1100 | -- This row is the current row of the current window. | 20 | SMITH | 800 | 800 | -- This row is the current row of the current window. | 30 | BLAKE | 2850 | 2850 | -- This row is the current row of the current window. | 30 | ALLEN | 1600 | 1600 | -- This row is the current row of the current window. | 30 | TURNER | 1500 | 1500 | -- This row is the current row of the current window. | 30 | MARTIN | 1250 | 1250 | -- This row is the current row of the current window. | 30 | WARD | 1250 | 1250 | -- This row is the current row of the current window. | 30 | JAMES | 950 | 950 | -- This row is the current row of the current window. +------------+------------+------------+-------------+
Related functions
LAST_VALUE is a window function. For more information about the functions that are used to calculate the sum of data of columns in a window and to sort data, see Window functions.