SQL MAX: Return the Maximum Value and Every Matching Row

The practice task

Find every fictional practice attempt tied for the highest score. The fixture intentionally has two highest-scoring attempts, so returning only one would lose a valid result.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE attempts(name TEXT, score INTEGER);
INSERT INTO attempts VALUES ('Ari',95),('Bo',80),('Cy',95),('Dee',NULL);

SELECT name, score FROM attempts
WHERE score = (SELECT MAX(score) FROM attempts)
ORDER BY name;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

The inner MAX result is 95. Both Ari and Cy match it, so both records appear. Dee has no measured score and does not match the comparison. The ordered output makes the two tied results easy to verify.

Selecting MAX(score) together with an ungrouped name is not a portable way to retrieve the desired record. Database products differ in whether they reject that query or permit special behavior. A clear comparison states which records qualify.

If a task requires exactly one winner, specify a secondary rule such as an attempt timestamp and a stable identifier. That rule answers a different question from returning every tie. Explain the choice rather than letting an accidental row order decide which person appears.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Distinguish the maximum value from the row or rows containing it.
• Test a tied maximum.
• Check how an all-missing input should be reported.