SQL FIRST_VALUE and LAST_VALUE: Emulated and Explained
As expected, the SQL FIRST_VALUE and LAST_VALUE window functions return the first and last value in their partition. However, prepare for some surprises along the way, especially for LAST_VALUE. We’ll show you how to call them properly and how to emulate them with ROWS BETWEEN.
Table of Contents
Visual SQL Query with the FIRST_VALUE Function
Download Data Xtractor for SQLite, and create the SQL query below with the visual query builder. As for the previous examples with LEAD and LAG, the query basically works on ANY relational database supporting window functions, as it is built with constants concatenated by UNION calls.
What it does is it selects all fruit names from a simple dynamic table built this way.
- First column returns all fruit names sorted in ascending order.
- Second column calls directly our FIRST_VALUE function, on a similar partition of sorted fruit names.
- Third column emulates our FIRST_VALUE function with the MIN aggregate OVER the same partition.
Second and third column both return the value at the beginning of that set. This works because the default partition uses a frame between UNBOUNDED PRECEDING (i.e. first row in partition) and the CURRENT ROW (i.e. current main query entry). No need to specify other limits, as both FIRST_VALUE and MIN will return in this context the exact same first value.
Generated SQL Query with FIRST_VALUE Call
Switch to SQL mode for the generated SQL query. We simplified the UNION SELECT subquery, to keep it short and focused.
WITH fruitsu_ AS (
SELECT 'Apples' AS "name"
UNION SELECT 'Oranges'
UNION SELECT 'Plums'
...
UNION SELECT 'Clementines')
SELECT fruitsu_."name" AS "name",
FIRST_VALUE(fruitsu_."name") OVER (ORDER BY fruitsu_."name") AS "FirstValue",
MIN(fruitsu_."name") OVER (ORDER BY fruitsu_."name") AS "Min"
FROM fruitsu_
ORDER BY 1
All window function calls are in the second SELECT subquery, while the top subquery returns the few rows with fruit names.
Remark the same OVER ORDER BY clauses after both FIRST_VALUE and MIN, which define partitions similar to the main query, with sorted fruit names. In real-life scenarios, the partitions may be different, but here, for demo purposes, it’s just easier to use the same set.
Visual SQL Query with the LAST_VALUE Function
The SQL query for LAST_VALUE looks similar, but we had to add a ROWS BETWEEN clause for both LAST_VALUE and its emulation with MAX.
- First column returns all fruit names sorted in ascending order.
- Second column uses our LAST_VALUE function, on a similar partition of sorted fruit names.
- Third column emulates our LAST_VALUE function with the MAX aggregate OVER the same partition.
Second and third column both return a value displayed at the end of the common set. We had however to specify a frame, because this is by default between UNBOUNDED PRECEDING (i.e. FIRST row in partition) and the CURRENT ROW (i.e. CURRENT main query entry). Without extending the frame to UNBOUNDED FOLLOWING (i.e. the actual LAST row in partition), we would get each time the current row value in this context.
The clause “RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING” is required in this example […] The default range is “RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”.
Generated SQL Query with LAST_VALUE Call
Switch to SQL mode for the generated SQL query. As for the FIRST_VALUE query, we simplified the UNION SELECT subquery, to keep it short and focused.
WITH fruitsu_ AS (
SELECT 'Apples' AS "name"
UNION SELECT 'Oranges'
UNION SELECT 'Plums'
...
UNION SELECT 'Clementines')
SELECT fruitsu_."name" AS "name",
LAST_VALUE(fruitsu_."name") OVER (ORDER BY fruitsu_."name"
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "LastValue",
MAX(fruitsu_."name") OVER (ORDER BY fruitsu_."name"
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "Max"
FROM fruitsu_
ORDER BY 1
Both window function lines could use the same ROWS BETWEEN clause, as long as the value we’re looking for is always the last one from any similar partition, in this context. It does not matter here if we start with the first row or the current row, as long as the value returned must always be the last.
How FIRST/LAST_VALUE Functions Actually Work
FIRST_VALUE looks at the first row in partition, with the Apples value, For this to happen, the frame must actually start with what we call UNBOUNDED PRECEDING. By default, a frame always starts with this value, so the ROWS clause is not necessary.
LAST_VALUE looks at the last row in partition, with the Plums value. For this to happen, the frame must actually end with what we call UNBOUNDED FOLLOWING. A frame ends by default with CURRENT ROW, so the ROWS clause is, in this context, necessary.
Unfortunately, no database we tested supports a ROWS clause between UNBOUNDED PRECEDING to UNBOUNDED PRECEDING (to return only the first row), or between UNBOUNDED FOLLOWING and UNBOUNDED FOLLOWING (to return only the last row).
This works however with specific offset values, between n PRECEDING and n PRECEDING, or n FOLLOWING and n FOLLOWING, as we shown for LEAD and LAG emulations.
Here is a more complete picture with all these so-called navigational category of window functions: FIRST_VALUE, LAST_VALUE, LAG and LEAD. FIRST_VALUE and LAG look backwards, while LAST_VALUE and LEAD look forward in the partition.
To be actually able to return the expected row value, make sure the frame is not limited by default. If it is, use an explicit ROWS BETWEEN clause.
Support and Compatibility Issues
FIRST_VALUE and LAST_VALUE called directly and emulated have been tested with Data Xtractor on several RDBMSs. They work well on the following relational database systems:
- SQLite – which supports window functions since 2018, with v3.25.
- Microsoft SQL Server – works well, also on Azure.
- Oracle – it works fine.
- IBM DB2 – works fine.
- PostgreSQL – ok. Amazon Redshift, which is based on Postgres, should work as well (not tested yet).
- MySQL – which supports window functions since v8.0.2. Amazon Aurora, which is MySQL-based, should work as well once they start using a more recent version.
- MariaDB – which supports window functions since v10.2.
- Firebird – the functions work fine, but the database does not support the ROWS/RANGE clause yet.
- SQL Anywhere – it may work as well, but not tested.