FIRST_VALUE and LAST_VALUE return a column from the first/last row of a window — e.g. each customer’s first and latest order amount, on every row.

SELECT
  customer_id,
  order_date,
  amount,
  FIRST_VALUE(amount) OVER w AS first_amount,
  LAST_VALUE(amount)  OVER w AS latest_amount
FROM orders
WINDOW w AS (
  PARTITION BY customer_id
  ORDER BY order_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

LAST_VALUE needs the full frame. With just ORDER BY, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so LAST_VALUE returns the current row, not the last one. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (as above) to see the whole partition. FIRST_VALUE is fine either way.

Dialect notes:

The inline WINDOW w AS (...) clause works in PostgreSQL, MySQL 8+, SQLite 3.25+, and DuckDB. BigQuery and Snowflake don’t support the named WINDOW clause — inline the OVER (...) on each function instead.
A common alternative to LAST_VALUE is FIRST_VALUE(... ORDER BY order_date DESC), which avoids the frame gotcha entirely.

First and last value per group (FIRST_VALUE / LAST_VALUE)