Concept · SQL & Querying

With ORDER BY and no frame clause, the window ends at the current row — so LAST_VALUE returns the current row's value, not the partition's last.

The default frame (RANGE UNBOUNDED PRECEDING TO CURRENT ROW) is asymmetric: everything up to here, nothing after. FIRST_VALUE never notices — the first row is always inside. LAST_VALUE asks for the frame's end, and the frame's end is the current row — so the column that looks like 'the season's final reading' is 'this row, echoed'. Plausible on sorted data, never errors, wrong on every row but the last.

See it break

runs

day	secs
1	50
2	45
3	60

The trap

SELECT
  day,
  LAST_VALUE (secs) OVER (
    ORDER BY
      day ) AS final
FROM
  runs
ORDER BY
  day;

The fix

SELECT
  day,
  LAST_VALUE (secs) OVER (
    ORDER BY
      day ROWS BETWEEN UNBOUNDED PRECEDING
      AND UNBOUNDED FOLLOWING
  ) AS final
FROM
  runs
ORDER BY
  day;

INPUT	Your query	The fix
1	50	60
2	45	60
3	60	60

Each day's 'season final' echoed that day's own time — with ORDER BY and no frame (the default), the window ends at the current row, so LAST_VALUE returns the current row, not the season's last; an explicit UNBOUNDED FOLLOWING frame fixes it.

The rule

Explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING; FIRST_VALUE over reversed order; MAX(...) OVER when 'last by order' is 'max by key'.

Shows up elsewhere

Default frame includes peer ties ROWS counts rows, RANGE counts time

Try one →Prove it in practice →