SQLite: Make last_value Read the End of the Partition
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 1Save 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.pyObserved 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 35Explain 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_dirAcknowledgements
This post was created with assistance from AI.