FIRST_VALUE
Returns the value of column from the first row of the window frame.
MySQL 8.1
FIRST_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 first; without it the first 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
Return value
A value of the same type as column
Examples
Oldest member in each status
MySQL 8.1
SELECT member_name,
status,
birthday,
FIRST_VALUE(member_name) OVER (
PARTITION BY status
ORDER BY birthday
) AS oldest_in_status
FROM FamilyMembers;Gap to the highest salary
MySQL 8.1
SELECT department_id,
name,
salary,
FIRST_VALUE(salary) OVER (
PARTITION BY department_id
ORDER BY salary DESC
) - salary AS gap_to_top
FROM employees;Details
The default frame starts at the first row of the partition, so without a custom frame the function returns the first value of the partition.
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.