Returns the value of the last row in the current window frame for the expression specified by expr.
Syntax
last_value(<expr> [, <ignore_nulls>]) over ([partition_clause] [orderby_clause] [frame_clause])Parameters
| Parameter | Required | Type | Default | Description |
|---|---|---|---|---|
expr | Yes | — | — | The expression used to calculate the return value. |
ignore_nulls | No | Boolean | false | Specifies whether to skip null values. When set to true, returns the last non-null value of expr in the window. |
partition_clause, orderby_clause, frame_clause | No | — | — | Window definition clauses. See windowing_definition. |
Return value
Returns a value of the same data type as expr.
Usage notes
How ORDER BY affects the window frame
The window frame changes depending on whether ORDER BY is specified:
Without ORDER BY: The window spans the entire partition (first row to last row).
last_valuereturns the same value — the partition's last row value — for every row in the partition.With ORDER BY: The window spans from the first row to the current row.
last_valuereturns the current row's own value.
Window function limits
Window functions are supported only in
SELECTstatements.A window function cannot contain nested window functions or aggregate functions.
Window functions and aggregate functions of the same level cannot be used together.
Examples
Set up sample data
The examples below use an emp table. Run the following to create and populate it:
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 to your data file.The emp.txt file contains the following rows:
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,,10Without ORDER BY
Partition employees by deptno and return the last sal value in each partition:
select deptno, ename, sal, last_value(sal) over (partition by deptno) as last_value from emp;Without ORDER BY, the window covers the entire partition. Every row in the same partition returns the value of the partition's last row.
+------------+------------+------------+-------------+
| 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 | -- Last row in partition 10
| 20 | FORD | 3000 | 2975 |
| 20 | SCOTT | 3000 | 2975 |
| 20 | SMITH | 800 | 2975 |
| 20 | ADAMS | 1100 | 2975 |
| 20 | JONES | 2975 | 2975 | -- Last row in partition 20
| 30 | TURNER | 1500 | 2850 |
| 30 | JAMES | 950 | 2850 |
| 30 | ALLEN | 1600 | 2850 |
| 30 | WARD | 1250 | 2850 |
| 30 | MARTIN | 1250 | 2850 |
| 30 | BLAKE | 2850 | 2850 | -- Last row in partition 30
+------------+------------+------------+-------------+With ORDER BY
Partition by deptno and order by sal descending:
select deptno, ename, sal, last_value(sal) over (partition by deptno order by sal desc) as last_value from emp;With ORDER BY, the window covers rows from the first row up to and including the current row. last_value returns the current row's value.
+------------+------------+------------+-------------+
| deptno | ename | sal | last_value |
+------------+------------+------------+-------------+
| 10 | JACCKA | 5000 | 5000 | -- Window: row 1 to current row
| 10 | KING | 5000 | 5000 | -- Window: row 1 to current row
| 10 | CLARK | 2450 | 2450 | -- Window: row 1 to current row
| 10 | WELAN | 2450 | 2450 | -- Window: row 1 to current row
| 10 | TEBAGE | 1300 | 1300 | -- Window: row 1 to current row
| 10 | MILLER | 1300 | 1300 | -- Window: row 1 to current row
| 20 | SCOTT | 3000 | 3000 | -- Window: row 1 to current row
| 20 | FORD | 3000 | 3000 | -- Window: row 1 to current row
| 20 | JONES | 2975 | 2975 | -- Window: row 1 to current row
| 20 | ADAMS | 1100 | 1100 | -- Window: row 1 to current row
| 20 | SMITH | 800 | 800 | -- Window: row 1 to current row
| 30 | BLAKE | 2850 | 2850 | -- Window: row 1 to current row
| 30 | ALLEN | 1600 | 1600 | -- Window: row 1 to current row
| 30 | TURNER | 1500 | 1500 | -- Window: row 1 to current row
| 30 | MARTIN | 1250 | 1250 | -- Window: row 1 to current row
| 30 | WARD | 1250 | 1250 | -- Window: row 1 to current row
| 30 | JAMES | 950 | 950 | -- Window: row 1 to current row
+------------+------------+------------+-------------+Related functions
last_value is a window function. For the full list of window functions and windowing clause syntax, see Window functions.