Use LEAD to inspect the next scheduled value

By Batu · English technical notes

Also published in our primary archive.

To inspect the next scheduled value in SQLite, use LEAD(at) OVER (ORDER BY at). For times 09:00, 11:00, and 15:00, it produces 11:00, 15:00, and NULL.

The naive self-join here joins every later time, not just the immediate successor. Consequently 09:00 occurs twice—once with 11:00 and once with 15:00. Adding grouping, a minimum, or more join conditions can repair that pattern, but it makes the intended adjacent-row relationship less direct. LEAD describes it explicitly: order the schedule, then look one row forward. The final row has no successor, so SQLite returns NULL; the assertion includes that boundary case.

The schema and seed list are printed so the result can be checked against the exact three-row input. SQLite specifies that LEAD returns the next row in the window partition and returns NULL when no such row exists in its window-functions documentation. The query is executed through Python’s standard-library sqlite3 module, backed only by :memory:. No newer Python-specific API is used (Python 3.5+); window functions require SQLite 3.25.0+.

AI-assistance disclosure: this synthetic example was drafted with AI assistance and is intended to be run and adapted by the reader.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE schedule(at TEXT PRIMARY KEY)")
seed = [("09:00",), ("11:00",), ("15:00",)]
con.executemany("INSERT INTO schedule VALUES (?)", seed)

print("schema:", con.execute(
    "SELECT sql FROM sqlite_master WHERE name = 'schedule'"
).fetchone()[0])
print("seed:", seed)
naive = list(con.execute("""
    SELECT current.at, later.at
    FROM schedule AS current
    LEFT JOIN schedule AS later ON later.at > current.at
    ORDER BY current.at, later.at
"""))
print("naive self-join:", naive)
corrected = list(con.execute("""
    SELECT at, LEAD(at) OVER (ORDER BY at) AS next_at
    FROM schedule ORDER BY at
"""))
print("corrected LEAD:", corrected)

assert corrected == [
    ("09:00", "11:00"),
    ("11:00", "15:00"),
    ("15:00", None),
]
con.close()

schema: CREATE TABLE schedule(at TEXT PRIMARY KEY)
seed: [('09:00',), ('11:00',), ('15:00',)]
naive self-join: [('09:00', '11:00'), ('09:00', '15:00'), ('11:00', '15:00'), ('15:00', None)]
corrected LEAD: [('09:00', '11:00'), ('11:00', '15:00'), ('15:00', None)]

Sources

SQLite SELECT