LAST_VALUE

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Returns <value> evaluated at the row that’s the last row of the window frame.

1last_value(<value>) [ RESPECT NULLS | IGNORE NULLS ] OVER (
2  [ PARTITION BY <expression> [, ...] ]
3  [ ORDER BY <expression> [, ...] ]
4  [ <frame_clause> ]
5)

• <value>: The expression to evaluate at the last row of the window frame.

• IGNORE NULLS: Skips NULL values within the window frame when determining the last non-NULL value. IGNORE NULLS is only supported for the last_value function.
• RESPECT NULLS: Considers NULL values within the window frame. This is the default behavior.

Returns the same type as <value>.

Null handling:

• RESPECT NULLS is the default and doesn’t ignore NULL values, so if the last row of the frame has a NULL <value>, the result is NULL.
• With IGNORE NULLS, last_value returns the most recent non-NULL <value> within the window frame. If every row in the frame has a NULL <value>, the result is NULL.

• last_value considers only the rows within the window frame, not the whole partition. By default, the frame contains the rows from the start of the partition through the last peer of the current row. If ORDER BY values tie, the frame includes all peer rows, so last_value can return a value from another peer. When ORDER BY uniquely orders rows, it returns the current row’s value. Add a tie-breaker to ORDER BY when you need deterministic results.
• To get the true last value across the entire partition regardless of the current row’s position, add an explicit frame such as ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to the OVER clause.
• IGNORE NULLS supports only the last_value function and isn’t available for other window functions.
• For full details on PARTITION BY, ORDER BY, and frame specifications, see Window Function Call Syntax. To name a window definition for reuse, see the WINDOW clause in SELECT.

A table imported from a spreadsheet can populate a column, such as country, on the first row of each group. Use last_value with IGNORE NULLS to fill in the gaps with the most recent non-NULL value.

Sample data:

1row_no | country | region | amount
2 -------+---------+--------+-------
3      1 | USA     | North  |   1000
4      2 | NULL    | East   |   1200
5      3 | NULL    | West   |   3000
6      4 | NULL    | South  |   2600
7      5 | Germany | North  |   1800
8      6 | NULL    | East   |   2700
9      7 | NULL    | West   |   1100
10      8 | NULL    | South  |   2100

1SELECT last_value(country) IGNORE NULLS OVER (ORDER BY row_no) AS country,
2   region,
3   amount
4FROM regions;

Result:

1country | region | amount
2--------+--------+-------
3USA     | North  |   1000
4USA     | East   |   1200
5USA     | West   |   3000
6USA     | South  |   2600
7Germany | North  |   1800
8Germany | East   |   2700
9Germany | West   |   1100
10Germany | South  |   2100

Add an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING frame to return the same, fixed last value for every row in a partition, instead of the current row’s own value.

1SELECT depname, empno, salary,
2  last_value(salary) OVER (PARTITION BY depname ORDER BY salary DESC
3    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest_salary
4FROM empsalary;

Because salary is ordered descending, the lowest salary is last in each department’s frame, so lowest_salary returns that salary for every row in the partition.

• FIRST_VALUE
• NTH_VALUE
• Aggregate Functions
• Window Functions