By Prakhar Shrivastava · October 13, 2026

LAST_VALUE reads the last row of its frame, not automatically the last row of the entire partition. In PostgreSQL, the default ordered frame ends at the current row’s last peer. With a unique ordering key, that means the current row. To repeat the final ordered value across a partition, specify a frame ending at UNBOUNDED FOLLOWING.

A five-record example

Suppose a fictional US and UK report contains dated revenue observations in USD. US values are 100, 150, and 80. UK values are 50 followed by an unknown amount. The question is: what is the final observed value in each market’s ordered history, displayed beside every source record?

Dates are consistent ISO strings within this fixture. Event IDs are unique and provide an agreed tie-breaker. In real data, use validated timestamp types and an explicit timezone. An arbitrary ID is not evidence of which simultaneous event happened first.

WITH events(market,event_id,day,revenue_usd) AS (VALUES
('US',1,'2026-09-01',100),('US',2,'2026-09-02',150),('US',3,'2026-09-03',80),
('UK',4,'2026-09-01',50),('UK',5,'2026-09-03',NULL))
SELECT market,event_id,revenue_usd,
LAST_VALUE(revenue_usd) OVER(PARTITION BY market ORDER BY day,event_id) AS default_last,
LAST_VALUE(revenue_usd) OVER(PARTITION BY market ORDER BY day,event_id
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS final_value
FROM events ORDER BY market,event_id

Read the output before changing the query

For US events 1, 2, and 3, default_last is 100, 150, and 80. The full-partition final_value is 80 on all three rows. The default result is not a broken function: each row has a different frame endpoint under the ordered default.

For UK event 4, the default value is 50. For event 5, it is NULL. With the explicit full frame, both UK rows have NULL as their final value because the final record’s revenue is unknown. The test checks all five output rows, including the NULLs. The query passed in SQLite 3.50.4; the linked PostgreSQL reference documents the frame behavior described here.

Why not use MAX instead?

MAX answers a value-order question: what is the largest known amount? LAST_VALUE with this frame answers a time-order question: what amount appears on the final ordered record? For the US fixture, the maximum is USD 150 while the final value is USD 80. A separate test confirms the maximum so the distinction cannot be hidden by an increasing example.

If the business question is the final status, balance, or observation, a maximum may be meaningless even when it produces a nonmissing result. State the ordering column and the identity of the selected final record when explaining your result.

Does the full frame skip NULL?

Not in this example. An unknown value on the last record remains unknown. PostgreSQL does not implement IGNORE NULLS for LAST_VALUE. “Final recorded value” and “most recent known value” are different requirements. If you need the latter, define how old a known observation may be and implement an explicit selection rule; do not silently replace unknown revenue with zero or an older amount.

Likewise, adding another field to the window ordering can change which record is last. Confirm whether timestamp ties should return one agreed record or all records at the final timestamp. The latest-row tie exercise covers that separate selection decision.

What rows are available to the window?

A full-partition frame covers the input rows available to the calculation. It does not restore history already removed by a WHERE clause. If you filter the source to January, the final value means the last included January observation. If you need the final value over a wider reporting window, calculate over that window before applying the display-only filter.

Using future rows can also be inappropriate for an as-of report. For example, showing September 3’s closing observation beside September 1 is valid for a retrospective comparison, but not as information known on September 1. Label the calculation accordingly and avoid using future observations as historical model features.

Checks to keep with the report

Confirm one row per intended source record, an agreed ordering and tie rule, the partition keys, and the selected frame endpoint. Test a final NULL and a sequence whose final amount is lower than its maximum. If several markets or accounts share a table, partition by the complete series identity.

Continue with the window-function reference, running totals with tied timestamps, and NULL versus zero guide. The primary source is the official PostgreSQL window-function reference.