LAST_VALUE
Returns the value of column from the last row of the window frame.
LAST_VALUE(column)- column
- Column or expression whose value is returned
Return value
A value of the same type as column
Examples
Youngest member in each status
SELECT member_name,
status,
birthday,
LAST_VALUE(member_name) OVER (
PARTITION BY status
ORDER BY birthday ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS youngest_in_status
FROM FamilyMembers;Default frame ends at the current row
SELECT member_name,
status,
birthday,
LAST_VALUE(member_name) OVER (
PARTITION BY status
ORDER BY birthday
) AS last_in_frame
FROM FamilyMembers;Details
The default frame ends at the current row (and rows with the same ORDER BY value), not at the end of the partition, so without a custom frame the function often returns the value of the current row. To get the last value of the partition, set the frame to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
The frame sets which rows of the partition the function sees and is written after ORDER BY: ROWS BETWEEN frame_start AND frame_end counts rows, while RANGE BETWEEN frame_start AND frame_end uses the ORDER BY values.
Without a frame, the default rule applies. If OVER has ORDER BY, the frame runs from the start of the partition to the current row, including rows with the same ORDER BY value. Without ORDER BY, the frame is the whole partition.