LAST_VALUE is a window (analytic) function that evaluates an expression for each row and returns the expression's value from the last row of the window frame defined by the OVER clause. Unlike aggregation functions, it does not collapse rows but instead adds a column with the result per row. The window frame defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, meaning the last row is the current row unless the frame is explicitly extended (e.g., ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING). Because LAST_VALUE is evaluated after the frame is applied, incorrect framing can lead to seemingly unexpected duplicates. NULLs are treated like any other value and ordering in the window clause dictates which row is considered "last."
value_expression
(Any) - The column or expression to return from the last row.partition_expression_list
(Any) - Optional expressions that define partitions.order_expression_list
(Any) - Required expressions that define ordering within each partition.frame_start / frame_end
(Keywords) - Define the window frame (e.g., UNBOUNDED PRECEDING, CURRENT ROW, UNBOUNDED FOLLOWING).FIRST_VALUE, LAG, LEAD, NTH_VALUE, WINDOW FUNCTIONS, OVER clause
SQL:2003
MAX aggregates all rows and returns a single value per group, collapsing rows. LAST_VALUE is a window function that keeps all rows and returns the value from the last ordered row in the frame.
Specify the frame as ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING in the OVER clause. This extends the frame to include all rows.
No. It treats NULL like any other value. If the last row in the frame has NULL, LAST_VALUE returns NULL.
Yes. If PARTITION BY is omitted, the entire result set is considered one partition.