LAST_VALUE
Returns the value of column from the last row of the window frame.
MySQL 8.1
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; without it the frame is the whole partition and the last row is arbitrary
- framekeywordoptional
- Window frame: ROWS or RANGE BETWEEN start AND end; from the start of the partition to the current row by default, the whole partition is ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
Return value
A value of the same type as column
Examples
Youngest member in each status
MySQL 8.1
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
MySQL 8.1
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.
The frame sets which rows of the partition the function sees and is written after ORDER BY: ROWS BETWEEN start AND end counts rows, while RANGE BETWEEN start AND 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.