Use a window row number for ranked fixture rows

By Batu · English technical notes

Use a window row number for ranked fixture rows

ROW_NUMBER() assigns a sequential position to each row in a window. This fixture has two divisions, and several fixtures share the same kickoff time. PARTITION BY division restarts numbering for each division. Inside each partition, ORDER BY kickoff, fixture_id ranks earlier kickoffs first and uses the unique fixture ID to resolve equal kickoff times. The outer query then orders the already-ranked rows by division and row number for a stable display.

For division A, fixtures 1 and 2 both start at 10:00. The fixture_id tie breaker makes 1 rank before 2, and fixture 3 receives rank 3. Division B independently starts at one. The assertion confirms these values on the supplied rows; it does not prove that the ordering represents a real scheduling policy. If equal values should share a rank, use RANK() or DENSE_RANK() instead of ROW_NUMBER(). Also choose an ordering that fully expresses the business rule, because changing it can change every assigned number.

The example uses Python's standard-library sqlite3 interface and no newer Python API, requiring Python 3 with sqlite3. SQLite window functions, including row_number(), require SQLite 3.25.0 or later; check sqlite3.sqlite_version_info when compatibility matters. Python documents that attribute and query execution, and SQLite documents window functions. Python sqlite3 documentation and SQLite window functions documentation are the official references.

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

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE fixture (division TEXT, fixture_id INTEGER, kickoff TEXT)")
con.executemany(
    "INSERT INTO fixture VALUES (?, ?, ?)",
    [("A", 2, "10:00"), ("A", 1, "10:00"), ("A", 3, "12:00"),
     ("B", 8, "09:00"), ("B", 7, "09:00")],
)

rows = con.execute(
    """
    SELECT division, fixture_id, row_number
    FROM (
        SELECT division, fixture_id,
               ROW_NUMBER() OVER (
                   PARTITION BY division
                   ORDER BY kickoff, fixture_id
               ) AS row_number
        FROM fixture
    )
    ORDER BY division, row_number
    """
).fetchall()

assert rows == [("A", 1, 1), ("A", 2, 2), ("A", 3, 3),
                ("B", 7, 1), ("B", 8, 2)]
con.close()
print(rows)

[('A', 1, 1), ('A', 2, 2), ('A', 3, 3), ('B', 7, 1), ('B', 8, 2)]