Why does LAST_VALUE(price) OVER (ORDER BY ts) return the current row's price?

## The symptom A developer wants each row of a price history to carry the product's most recent price and writes: ```sql SELECT product_id, ts, price, LAST_VALUE(price) OVER (PARTITION BY product_id ORDER BY ts) AS latest_price FROM price_history; ``` Every row comes back with `latest_price` equal to its own `price`. Nothing errors; the column is simply a copy. This is the single most-asked trap about value functions. ## Why it happens Window functions that read a *position* inside the window — `FIRST_VALUE`, `LAST_VALUE`, `NTH_VALUE`, and all aggregates used with `OVER` — operate on the **frame**, a sub-range of the partition computed per row. The frame is defined by the frame clause, and when you omit it the standard supplies one: - `OVER (ORDER BY ...)` with no frame clause → `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`. - `OVER ()` or `OVER (PARTITION BY ...)` with **no** `ORDER BY` → the frame is the entire partition. So as soon as you add `ORDER BY` — which you must, for "last" to mean anything — the frame silently becomes everything from the start of the partition up to the current row. The last row of that frame is the current row. `LAST_VALUE` faithfully returns it. There is a second-order wrinkle: the default frame uses `RANGE`, not `ROWS`. `RANGE ... CURRENT ROW` extends to include all *peers* — rows whose `ORDER BY` values are equal to the current row's. So with tied timestamps, `LAST_VALUE` returns the value from the last row of the tie group, which can differ from the current row and looks even more mysterious. ## The fixes **Widen the frame explicitly.** This is the direct fix and it says what you mean: ```sql LAST_VALUE(price) OVER ( PARTITION BY product_id ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS latest_price ``` **Reverse the ordering and take the first value.** `FIRST_VALUE` is immune, because the default frame always begins at `UNBOUNDED PRECEDING` — the first row of the partition is always inside it: ```sql FIRST_VALUE(price) OVER (PARTITION BY product_id ORDER BY ts DESC) AS latest_price ``` Many practitioners prefer this form precisely because it cannot be broken by forgetting a frame. Watch the consequences of `DESC`, though: it also changes where NULLs sort, and it changes the meaning of any other function sharing that window. ## What it is not `LAST_VALUE(price)` is not `MAX(price)`. "The most recent price" and "the highest price" are different questions; the ordering column decides which row is last, and the argument decides which value comes back from it. Using `MAX` because it "seems to work" on a monotonic column is a bug waiting for the first price cut. It is also not the same family as `LAG`/`LEAD`. Offset functions address rows by position in the partition and are not governed by a frame clause at all — the standard does not permit a frame specification with them. So in one query, a `LAG` and a `LAST_VALUE` sharing the same `ORDER BY` respond differently to an added frame clause. Knowing which functions are frame-sensitive is the durable takeaway. ## NTH_VALUE inherits the same rule `NTH_VALUE(price, 3)` returns the value from the third row **of the frame**, so under the default frame it is NULL until the frame has grown to three rows, then it becomes stable. If you want the third row of the whole partition, widen the frame the same way. The standard also defines `FROM FIRST` / `FROM LAST` for `NTH_VALUE`, but engine support varies — check before depending on it. ## How to spot it in review A reliable heuristic: any `LAST_VALUE` or `NTH_VALUE` whose `OVER` clause contains `ORDER BY` but no frame clause is almost certainly wrong. Either the author wants the partition's end, in which case the frame is missing, or they want a running endpoint, in which case they should say so explicitly. Reviewers who know only this one rule catch most occurrences.