FIRST_VALUE
Returns the value of column from the first row of the window frame.
PostgreSQL 17.9
FIRST_VALUE(column)- column
- Column or expression whose value is returned
Return value
A value of the same type as column
Examples
Oldest member in each status
PostgreSQL 17.9
SELECT member_name,
status,
birthday,
FIRST_VALUE(member_name) OVER (
PARTITION BY status
ORDER BY birthday
) AS oldest_in_status
FROM FamilyMembers;Difference from the first purchase
PostgreSQL 17.9
SELECT family_member,
date,
unit_price,
unit_price - FIRST_VALUE(unit_price) OVER (
PARTITION BY family_member
ORDER BY date
) AS diff_from_first
FROM Payments
ORDER BY family_member,
date;Details
The default frame always starts at the first row of the partition, so without an explicit frame the function returns the first value of the partition. How to set another frame is described in ROWS BETWEEN.