FIRST_VALUE & LAST_VALUE

FIRST_VALUE() and LAST_VALUE() are window functions that return the value of an expression from the first or last row of the current window frame, respectively. They are useful for questions like "what was the opening price of the day?" or "what is the most recent status for this record, shown next to every row?"

SQL

FIRST_VALUE(expression) OVER (PARTITION BY ... ORDER BY ... [frame_clause])
LAST_VALUE(expression)  OVER (PARTITION BY ... ORDER BY ... [frame_clause])

A straightforward FIRST_VALUE example

Given hourly stock price ticks for a single day:

SQL

tick_time | price
----------+------
09:00     | 100
10:00     | 103
11:00     | 101
12:00     | 105

SQL

SELECT
  tick_time,
  price,
  FIRST_VALUE(price) OVER (ORDER BY tick_time) AS opening_price
FROM stock_ticks;

tick_time | price | opening_price
----------+-------+--------------
09:00     | 100   | 100
10:00     | 103   | 100
11:00     | 101   | 100
12:00     | 105   | 100

FIRST_VALUE() behaves as expected here: every row sees the same opening price, 100, taken from the first row of the window.

The LAST_VALUE trap

Trying the equivalent query with LAST_VALUE() to fetch the day's closing price often surprises people the first time:

SQL

SELECT
  tick_time,
  price,
  LAST_VALUE(price) OVER (ORDER BY tick_time) AS closing_price
FROM stock_ticks;

tick_time | price | closing_price
----------+-------+--------------
09:00     | 100   | 100
10:00     | 103   | 103
11:00     | 101   | 101
12:00     | 105   | 105

Warning

This is not a bug — it's the default window frame at work. When ORDER BY is present but no explicit frame clause is given, most databases default the frame to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That means, from each row's point of view, the "window" only extends up to itself — so LAST_VALUE() just returns the current row's own value every time, not the true last row of the whole partition.

The fix: an explicit frame clause

To make LAST_VALUE() see the entire partition regardless of which row is currently being evaluated, extend the frame explicitly to UNBOUNDED FOLLOWING:

SQL

SELECT
  tick_time,
  price,
  LAST_VALUE(price) OVER (
    ORDER BY tick_time
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS closing_price
FROM stock_ticks;

tick_time | price | closing_price
----------+-------+--------------
09:00     | 100   | 105
10:00     | 103   | 105
11:00     | 101   | 105
12:00     | 105   | 105

Now every row correctly sees 105, the price from the last tick of the day, as the closing price. The frame clause ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING tells the database to consider the whole partition for every row, not just the rows up to the current one.

Tip

FIRST_VALUE() does not have this problem in the example above because UNBOUNDED PRECEDING AND CURRENT ROW already includes the first row for every subsequent row — the default frame happens to align with what FIRST_VALUE() needs. LAST_VALUE() is the one that almost always needs the frame widened explicitly.

Partitioned example

Combine PARTITION BY with the corrected frame to get, say, the first and last price of the day per stock symbol:

SQL

SELECT
  symbol,
  tick_time,
  price,
  FIRST_VALUE(price) OVER (
    PARTITION BY symbol ORDER BY tick_time
  ) AS opening_price,
  LAST_VALUE(price) OVER (
    PARTITION BY symbol ORDER BY tick_time
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS closing_price
FROM stock_ticks;

Note

An alternative to LAST_VALUE() with a widened frame is FIRST_VALUE() combined with ORDER BY ... DESC — reversing the order turns "last" into "first". Some engineers prefer this because it avoids having to remember the frame-clause fix at all.