SQLite 3 reference · Window Functions
SQLite LAST_VALUE() Function
Return a value from the last row of the window frame. Widen the frame to see the real last row of the partition.
SQLite 3 Since 3.25.0
Syntax
last_value(X) OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
Parameters
| Parameter | Description |
|---|---|
X |
A column or expression. |
Return value
The value from the frame's last row.
Examples
SELECT name, salary,
last_value(name) OVER (ORDER BY salary) AS default_frame,
last_value(name) OVER (ORDER BY salary
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS whole_partition
FROM employees
WHERE department = 'Sales';
Output
name | salary | default_frame | whole_partition Emma Novak | 88000 | Emma Novak | Hannah Okafor Diego Silva | 90000 | Diego Silva | Hannah Okafor Hannah Okafor | 150000 | Hannah Okafor | Hannah Okafor
SELECT DISTINCT department,
last_value(hire_date) OVER (PARTITION BY department ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS latest_hire
FROM employees;
Output
department | latest_hire Engineering | 2023-08-28 Management | 2018-03-01 Sales | 2024-01-08 Support | 2024-10-01
Common mistakes
- With an
ORDER BY, the default frame ends at the current row, solast_value()returns the current row's value. AddROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
Version history
| Version | Change |
|---|---|
| SQLite 3.25.0 | Added |
In other languages
LAST_VALUE()· PostgreSQL
Related
FIRST_VALUE()· Return a value from the first row of the window frame, such as each department's highest salary.NTH_VALUE()· Return a value from the Nth row of the window frame, or NULL if the frame has fewer rows.LEAD()· Return a value from a following row in the window, such as the next order's date, or a default when there isn't one.- SQL Window Functions · tutorial