Order deterministic query output with a tie breaker

By Batu · English technical notes

Also published in our primary archive.

Order deterministic query output with a tie breaker

Sorting only by a non-unique value does not specify an order among rows with equal values. The task fixture has two priority-one tasks and two priority-two tasks. ORDER BY priority, task_id makes priority the primary ordering rule and the unique task_id the tie breaker. The output is therefore build, test, lint, package: within each priority, task IDs rise numerically.

The assertion checks the selected columns and their intended sequence. It is important that the secondary key matches the product rule: a creation timestamp, name, or identifier can each define a different order. If task_id is not unique, add enough additional ordering expressions to make the desired ordering complete. A tie breaker controls the returned order; it does not make two tasks semantically equivalent, validate data quality, or guarantee a particular execution plan. Likewise, an ORDER BY in a different query does not carry into this one.

sqlite3.connect(":memory:") isolates the example to a process-local database, and fetchall() returns rows as Python tuples. No newer Python-specific API is used, so it needs Python 3 with the standard sqlite3 module. The actual SQL behavior is supplied by the SQLite library available to that Python installation. The official Python reference covers executing statements and fetching result rows; SQLite's SELECT documentation covers ordering terms. Python sqlite3 documentation and SQLite SELECT documentation are cited here.

AI assistance disclosure: AI assisted the drafting of this example and explanation.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE task (task_id INTEGER, priority INTEGER, label TEXT)")
con.executemany(
    "INSERT INTO task VALUES (?, ?, ?)",
    [(30, 2, "lint"), (10, 1, "build"),
     (20, 1, "test"), (40, 2, "package")],
)

rows = con.execute(
    """
    SELECT task_id, priority, label
    FROM task
    ORDER BY priority, task_id
    """
).fetchall()

assert rows == [
    (10, 1, "build"),
    (20, 1, "test"),
    (30, 2, "lint"),
    (40, 2, "package"),
]
con.close()
print(rows)

[(10, 1, 'build'), (20, 1, 'test'), (30, 2, 'lint'), (40, 2, 'package')]