Skip to content
SQLite: Make last_value Read the End of the Partition

SQLite: Make last_value Read the End of the Partition

November 15, 2026

A sensor report needs each reading beside that sensor’s final reading. An ordered last_value call can return the current reading instead. The frame must extend to the end of the partition to answer this question.

Compare the two frames

This example keeps every reading and calculates two last values. The unique ID breaks timestamp ties. All tables exist only in memory.

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3.11 or later with SQLite support, a text editor, and standard filesystem tools. No extra packages or SQLite CLI are required.

Create a scratch directory in one terminal. Stop if setup fails before saving the example.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

Save as last-value-frame.py:

import sqlite3

db = sqlite3.connect(":memory:")
try:
    db.executescript("""
        CREATE TABLE readings (
            id INTEGER PRIMARY KEY, sensor TEXT, minute INTEGER, value INTEGER
        );
        INSERT INTO readings VALUES
            (1, 'A', 10, 20), (2, 'A', 20, 25), (3, 'A', 30, 18),
            (4, 'B', 10, 40), (5, 'B', 20, 35);
    """)
    rows = db.execute("""
        SELECT sensor, minute, value,
            last_value(value) OVER (
                PARTITION BY sensor ORDER BY minute, id
            ) AS default_last,
            last_value(value) OVER (
                PARTITION BY sensor ORDER BY minute, id
                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
            ) AS final_value
        FROM readings
        ORDER BY sensor, minute, id
    """)
    print("sensor minute value default_last final_value")
    for row in rows:
        print(*row)
finally:
    db.close()

Run:

python3 last-value-frame.py

Observed output:

sensor minute value default_last final_value
A 10 20 20 18
A 20 25 25 18
A 30 18 18 18
B 10 40 40 35
B 20 35 35 35

Explain the result

SQLite’s default ordered frame extends from the start of the partition through the current row and its peers. Peers share all window ordering values. Because the example orders by minute, id, each row has a distinct ordering key. The default frame therefore ends at that row, and default_last equals its value. SQLite frame specifications.

The explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING frame covers the whole sensor partition. Sensor A ends at 18 and sensor B ends at 35, so those values appear beside every reading from their respective sensors. last_value respects the frame, unlike functions such as lag that use positions in the partition. Built-in window functions.

Keep a deterministic ordering key when the last value must identify one particular row. Ordering by a non-unique timestamp alone leaves ties unresolved. The outer ORDER BY controls display order; it does not supply a missing window tie-breaker.

The final reading is not the largest reading: sensor A reaches 25 before ending at 18. A maximum would answer a different reporting question. This query also retains a NULL final value if the final row contains NULL; it does not search backward for a non-null reading.

Clean up

In the same terminal, remove only the example file and the empty scratch directory:

rm -- last-value-frame.py
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.

Last updated on