SQLite 3 reference · Window Functions
SQLite FIRST_VALUE() Function
Return a value from the first row of the window frame, such as each department's highest salary.
SQLite 3 Since 3.25.0
Syntax
first_value(X) OVER (PARTITION BY ... ORDER BY ...)
Parameters
| Parameter | Description |
|---|---|
X |
A column or expression. |
Return value
The value from the frame's first row.
Examples
SELECT department, name, salary,
first_value(name) OVER (PARTITION BY department ORDER BY salary DESC) AS top_earner
FROM employees
WHERE department IN ('Sales', 'Support');
Output
department | name | salary | top_earner Sales | Hannah Okafor | 150000 | Hannah Okafor Sales | Diego Silva | 90000 | Hannah Okafor Sales | Emma Novak | 88000 | Hannah Okafor Support | Ravi Kumar | 65000 | Ravi Kumar
SELECT name, salary,
salary - first_value(salary) OVER (ORDER BY salary) AS above_lowest
FROM employees
LIMIT 3;
Output
name | salary | above_lowest Ravi Kumar | 65000 | 0 Emma Novak | 88000 | 23000 Diego Silva | 90000 | 25000
Version history
| Version | Change |
|---|---|
| SQLite 3.25.0 | Added |
In other languages
FIRST_VALUE()· PostgreSQL
Related
LAST_VALUE()· Return a value from the last row of the window frame. Widen the frame to see the real last row of the partition.NTH_VALUE()· Return a value from the Nth row of the window frame, or NULL if the frame has fewer rows.LAG()· Return a value from a previous row in the window, such as last month's total, or a default when there isn't one.- SQL Window Functions · tutorial