LAST_VALUE
Returns the value of column from the last row of the window frame.
PostgreSQL 17.5
LAST_VALUE(column) OVER ([PARTITION BY ...] [ORDER BY ...] [frame])Parameters
- column
- Column or expression whose value is returned
- PARTITION BYoptional
- Columns that split rows into partitions
- ORDER BYoptional
- Order of rows in the partition that defines which row is last
- framekeywordoptional
- Window frame: ROWS, RANGE or GROUPS BETWEEN start AND end; by default, with ORDER BY, from the start of the partition to the current row together with rows equal to it in ORDER BY, without ORDER BY, the whole partition; the whole partition explicitly is ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
Return value
A value of the same type as column
Examples
Youngest member in each status
PostgreSQL 17.5
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.5
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. How to set a frame is described in ROWS BETWEEN.