LAST_VALUE
Returns the value of column from the last row of the window frame.
PostgreSQL 17.9
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
PostgreSQL 17.9
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
PostgreSQL 17.9
SELECT member_name,
status,
birthday,
LAST_VALUE(member_name) OVER (
PARTITION BY status
ORDER BY birthday
) AS last_in_frame
FROM FamilyMembers;Details
With ORDER BY 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 an explicit frame the function often returns the value of the current row; without ORDER BY the frame is the whole partition. To get the last value of the whole partition, set the frame ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.