Set an explicit whole-partition frame for SQLite last_value

By Batu · English technical notes

Also published in our primary archive.

To make SQLite last_value return the final value in an ordered partition, specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. With account A’s values 10, 20, 30, the explicit frame returns 30 on every row.

The surprising version is the default ordered window frame. The first last_value expression has PARTITION BY account ORDER BY sequence but no frame. Its frame ends at the current row’s peer group, so its displayed “last” value is 10, then 20, then 30. That result is correct for its frame, yet wrong if the intended question is “what is this account’s final value?” The second expression uses the same partition and ordering but expands the frame through the partition’s final row. The assertion compares the two complete result tables.

ORDER BY therefore does two jobs here: it defines the timeline and participates in the default frame choice. Use ROWS explicitly when row-by-row boundaries are what you mean, especially if a sequence can have tied values. This example has a unique integer sequence so its output is easy to audit; it does not establish behavior for missing, tied, or null ordering values.

The Python code uses only standard-library sqlite3 and no newer Python-only API. SQLite window functions require SQLite 3.25.0 or later. SQLite window-functions documentation Python sqlite3 documentation

AI assistance disclosure: this synthetic example and explanation were prepared with AI assistance.

Example

import sqlite3


connection = sqlite3.connect(":memory:")
connection.executescript(
    """
    CREATE TABLE balances (
        account TEXT,
        sequence INTEGER,
        value INTEGER
    );
    """
)
connection.executemany(
    "INSERT INTO balances VALUES (?, ?, ?)",
    [("A", 1, 10), ("A", 2, 20), ("A", 3, 30)],
)

rows = list(connection.execute(
    """
    SELECT sequence,
           value,
           last_value(value) OVER (
               PARTITION BY account ORDER BY sequence
           ) AS default_last,
           last_value(value) OVER (
               PARTITION BY account ORDER BY sequence
               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
           ) AS partition_last
    FROM balances
    ORDER BY sequence
    """
))

assert rows == [(1, 10, 10, 30), (2, 20, 20, 30), (3, 30, 30, 30)]
print("sequence value default_last partition_last")
for row in rows:
    print(*row)

Expected output:

sequence value default_last partition_last
1 10 10 30
2 20 20 30
3 30 30 30