SQL Reference ยท Window Functions
SQL FIRST_VALUE(), LAST_VALUE() and NTH_VALUE()
Reference for SQL FIRST_VALUE(), LAST_VALUE() and NTH_VALUE(): pick a value from a window frame, the LAST_VALUE default frame trap, and how to fix it.
FIRST_VALUE(), LAST_VALUE() and NTH_VALUE() return an expression evaluated at a specific row of the window frame: the first row, the last row, or the n-th row. Use them to compare every row with the cheapest item of its category, the earliest order of a customer or the highest score in a class, while keeping all rows.
Unlike LAG() and LEAD(), which are relative to the current row, these three are anchored to the edges of the frame, and they obey the frame clause. That makes LAST_VALUE() the classic surprise: with the default frame, the last row is the current row (or its last peer), not the last row of the partition.
SELECT name, price, FIRST_VALUE(name) OVER (PARTITION BY category_id ORDER BY price) AS cheapest_in_category FROM products;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
FIRST_VALUE(expression) OVER (window_definition) LAST_VALUE(expression) OVER (window_definition) NTH_VALUE(expression, n) OVER (window_definition) -- where window_definition is [ PARTITION BY ... ] ORDER BY ... [ { ROWS | RANGE | GROUPS } BETWEEN ... AND ... ]
| Part | Description |
|---|---|
expression |
The value to return from the chosen row. |
n |
For NTH_VALUE: a positive integer, counted from 1 at the start of the frame. |
ORDER BY |
Defines which row is first and which is last. |
| frame | Restricts the rows considered. Without one, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when there is an ORDER BY. |
Returns: the value of expression at the chosen row, or NULL if the frame has fewer than n rows (for NTH_VALUE).
Dialect differences
| Feature | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
FIRST_VALUE, LAST_VALUE |
3.25+ | 8.4+ | 8.0+ | 2012+ | Yes |
NTH_VALUE |
3.25+ | 8.4+ | 8.0+ | No | 11gR2+ |
IGNORE NULLS |
No | No | No | 2022+ (first and last only) | Yes |
FROM LAST for NTH_VALUE |
No | No | No | No | Yes |
-- Oracle: skip NULLs and count from the end SELECT NTH_VALUE(price, 2) FROM LAST IGNORE NULLS OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM products; -- SQL Server has no NTH_VALUE: emulate it with ROW_NUMBER() in a subquery
Usage notes
LAST_VALUE()needs an explicit frame. WithORDER BYand no frame, the frame stops at the current row (and its peers), so you get the current value back. AddROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or flip the order and useFIRST_VALUE().FIRST_VALUE()with the default frame is fine, because the frame always starts at the partition beginning.NTH_VALUE()isNULLuntil the frame is big enough. With the default frame, the second row is the first to show a value forn = 2.- Always sort deterministically. Ties make "first" and "last" arbitrary, so end the
ORDER BYwith a unique column. NULLvalues count as rows. WithoutIGNORE NULLS,FIRST_VALUE(x)isNULLif the first row'sxisNULL.- A simpler alternative for one row per group is
ROW_NUMBER() ... = 1in a subquery, which returns the entire row rather than one column. - They do not aggregate. Each input row still gets an output row; use
MIN()/MAX()withGROUP BYfor one row per group.
Related
LAG()andLEAD(), window frames,ROW_NUMBER(),OVER()- Lessons: Window frames, LAG and LEAD