Detect ties for a maximum value with RANK

By Batu · English technical notes

To detect every tie for a maximum value in SQLite, assign RANK() in descending score order and filter for rank 1. For scores 9, 9, 7, that returns both score-9 rows.

The fixture labels the rows Ada, Ben, and Cy so the missing tie is unmistakable. The naïve query uses ORDER BY score DESC LIMIT 1; it returns only Ada because the secondary name ordering makes one row first. That query can choose one representative, but it cannot prove that the representative is the sole maximum. The corrected query ranks all rows first, then filters the derived result where score_rank = 1. Its two-row assertion is the behavioral contract.

RANK() is significant here. Rows tied on the ordering expression receive the same rank, and the next rank may have a gap. If you need a sequential number for every row, use ROW_NUMBER() instead, but filtering ROW_NUMBER() = 1 would again hide the tie. If you want only one winner with a documented tie-breaker, LIMIT 1 can be appropriate; it answers a different question from detecting all maximum holders.

This uses Python’s standard-library sqlite3 module and no newer Python-only API. SQLite window functions, including RANK, require SQLite 3.25.0 or later. SQLite window-functions documentation SQLite SELECT documentation

AI assistance disclosure: this synthetic example and explanation were prepared with AI assistance.

Example

import sqlite3


connection = sqlite3.connect(":memory:")
connection.executescript(
    """
    CREATE TABLE scores (
        name TEXT,
        score INTEGER
    );
    """
)
connection.executemany(
    "INSERT INTO scores VALUES (?, ?)",
    [("Ada", 9), ("Ben", 9), ("Cy", 7)],
)

schema = [row[0] for row in connection.execute(
    "SELECT sql FROM sqlite_master WHERE type = 'table'"
)]
seed = list(connection.execute("SELECT name, score FROM scores ORDER BY name"))
naive = list(connection.execute(
    "SELECT name, score FROM scores ORDER BY score DESC, name LIMIT 1"
))
corrected = list(connection.execute(
    """
    SELECT name, score
    FROM (
        SELECT name, score, RANK() OVER (ORDER BY score DESC) AS score_rank
        FROM scores
    )
    WHERE score_rank = 1
    ORDER BY name
    """
))

assert naive == [("Ada", 9)]
assert corrected == [("Ada", 9), ("Ben", 9)]
print("schema:", schema)
print("seed:", seed)
print("LIMIT 1:", naive)
print("RANK() = 1:", corrected)

Expected output:

schema: ['CREATE TABLE scores (\n        name TEXT,\n        score INTEGER\n    )']
seed: [('Ada', 9), ('Ben', 9), ('Cy', 7)]
LIMIT 1: [('Ada', 9)]
RANK() = 1: [('Ada', 9), ('Ben', 9)]