PostgreSQL 18 reference · Window Functions
PostgreSQL FIRST_VALUE() Function
Return a value from the first row of the window frame, such as each department's highest salary.
PostgreSQL 18 Since 8.4
Syntax
first_value(value) OVER (PARTITION BY ... ORDER BY ...)
Parameters
| Parameter | Description |
|---|---|
value |
The expression to read. |
Return value
The value.
Examples
SELECT department, name, first_value(name) OVER (PARTITION BY department ORDER BY salary DESC) AS top_earner
FROM employees
WHERE department IN ('Sales', 'Support');
Output
department | name | top_earner ------------+---------------+--------------- Sales | Hannah Okafor | Hannah Okafor Sales | Diego Silva | Hannah Okafor Sales | Emma Novak | Hannah Okafor Support | Ravi Kumar | Ravi Kumar (4 rows)
Run this on your computer — see “Get Started” for setup.
SELECT name, salary, salary - first_value(salary) OVER (ORDER BY salary) AS above_lowest
FROM employees
ORDER BY salary
LIMIT 3;
Output
name | salary | above_lowest -------------+--------+-------------- Ravi Kumar | 65000 | 0 Emma Novak | 88000 | 23000 Diego Silva | 90000 | 25000 (3 rows)
Run this on your computer — see “Get Started” for setup.
Version history
| Version | Change |
|---|---|
| PostgreSQL 8.4 | Added |
In other languages
FIRST_VALUE()· SQLite
Related
LAST_VALUE()· Return a value from the last row of the window frame. Widen the frame or it returns the current row.NTH_VALUE()· Return a value from the nth row of the window frame, or NULL if the frame has fewer rows.- SQL Window Functions · tutorial