Use a correlated subquery for a per-group maximum

By Batu · English technical notes

Use a correlated subquery for a per-group maximum

A per-group maximum asks for the complete rows whose value is greatest within each group. Here, score contains players and points for two teams. The inner SELECT MAX(peer.points) is correlated because peer.team = s.team refers to the current outer row s. SQLite therefore calculates the maximum for that row's team, and the outer WHERE retains the row when its points equal that maximum.

The concrete result deliberately includes both Blue players: Cy and Dee each have nine points, Blue's maximum. This is a useful difference from choosing one arbitrary row per team. The assertion verifies the result for this fixture, including the tie, while ORDER BY s.team, s.player makes the printed sequence exact. Without that final ordering, a query result has no defined presentation order. For larger tables, inspect the query plan and consider indexes appropriate to both the grouping key and compared value; this example does not establish a performance result.

The standard-library sqlite3.connect(":memory:") creates a temporary in-memory database, and executemany() binds each tuple using placeholders. This uses no newer Python-specific API; it requires Python 3 with sqlite3 available. SQL aggregate and subquery behavior depends on the SQLite library bundled with or linked to that Python build. The Python documentation describes parameter binding and cursor results, while SQLite documents SELECT expressions and subqueries. Python sqlite3 documentation and SQLite SELECT documentation are the relevant references.

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

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE score (team TEXT, player TEXT, points INTEGER)")
con.executemany(
    "INSERT INTO score VALUES (?, ?, ?)",
    [("red", "Ada", 12), ("red", "Bo", 18),
     ("blue", "Cy", 9), ("blue", "Dee", 9)],
)

rows = con.execute(
    """
    SELECT s.team, s.player, s.points
    FROM score AS s
    WHERE s.points = (
        SELECT MAX(peer.points)
        FROM score AS peer
        WHERE peer.team = s.team
    )
    ORDER BY s.team, s.player
    """
).fetchall()

assert rows == [("blue", "Cy", 9), ("blue", "Dee", 9), ("red", "Bo", 18)]
con.close()
print(rows)

[('blue', 'Cy', 9), ('blue', 'Dee', 9), ('red', 'Bo', 18)]