Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT name, price,
       FIRST_VALUE(name) OVER (PARTITION BY category_id ORDER BY price) AS cheapest_in_category
FROM products;

Try it

SQL Compare every product with the cheapest in its category ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL The LAST_VALUE trap, and the fix ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL NTH_VALUE: the second cheapest product ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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
SQL
-- 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. With ORDER BY and no frame, the frame stops at the current row (and its peers), so you get the current value back. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or flip the order and use FIRST_VALUE().
  • FIRST_VALUE() with the default frame is fine, because the frame always starts at the partition beginning.
  • NTH_VALUE() is NULL until the frame is big enough. With the default frame, the second row is the first to show a value for n = 2.
  • Always sort deterministically. Ties make "first" and "last" arbitrary, so end the ORDER BY with a unique column.
  • NULL values count as rows. Without IGNORE NULLS, FIRST_VALUE(x) is NULL if the first row's x is NULL.
  • A simpler alternative for one row per group is ROW_NUMBER() ... = 1 in 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() with GROUP BY for one row per group.