How can I find every row tied for the highest value in a group?
Quick answer
First calculate the maximum value for each group, then join those maxima back to the original table using both the group key and the value. This returns every original row matching its group’s maximum, including ties. A grouped MAX() by itself returns one summary row per group, not all the records that reached that value. In SQLite, avoid relying on a bare column beside MAX() to choose tied rows; it may select an arbitrary one. [S1]
The question, the details, the next step.
I have contest results and want every player who shares the highest score in each contest. Why doesn't SELECT contest, MAX(score) FROM results GROUP BY contest give me the rows I need?
That query answers a summary question: it gives you one maximum score for each contest. It does not return every underlying result row that has that score. A group can have two or more players tied at the maximum, so you need to compare the original rows against the per-contest maximum. [S1]
Suppose results has these columns: contest, player, and score. The following SQLite query returns the full records represented by those columns for all top scorers:
SELECT r.contest, r.player, r.score
FROM results AS r
JOIN (
SELECT contest, MAX(score) AS highest_score
FROM results
GROUP BY contest
) AS maxima
ON r.contest = maxima.contest
AND r.score = maxima.highest_score
ORDER BY r.contest, r.player;The join matches on both the contest and its maximum score. Matching only on score could incorrectly include a player from another contest that happened to have the same number.
Can you show what that returns when there is an actual tie, and explain which rows the query keeps?
Assume these are invented example rows:
contest player score
Autumn Cup Ana 12
Autumn Cup Bo 15
Autumn Cup Cy 15
Spring Cup Dee 9
Spring Cup Eli 7The grouped subquery finds 15 for Autumn Cup and 9 for Spring Cup. Joining those values back to results keeps every row whose contest and score both match a maximum. The output is:
contest player score
Autumn Cup Bo 15
Autumn Cup Cy 15
Spring Cup Dee 9Bo and Cy both remain because neither row is discarded when the maximum is tied. The ORDER BY makes the display predictable; without it, do not assume a particular row order. SQLite’s documentation recommends joining the per-group maximum back to the records, or using a correlated maximum condition, to get the full rows. [S1]
Could I add player to the grouped query instead, like SELECT contest, player, MAX(score) ... GROUP BY contest?
That is not a reliable way to retrieve every tied winner. In SQLite, a bare column such as player alongside MAX(score) can come from an arbitrary tied row; the query still does not return all tied records. [S1] Use the join when you need the full rows, including player names or other columns.
If you only need the winning values, the grouped summary is enough. If you need each winning record, keep the original table in the outer query and match it to the group’s maximum, as in the example. This distinction helps avoid confusing “what is the maximum?” with “which records have that maximum?”
What should I check before adapting this to my table, and what if I only want the winners’ names?
Check that contest identifies the group you mean and that score is the value you want to maximize. Replace those example names with your actual column and table names. Keep every column that makes a group distinct in both the subquery’s GROUP BY and the join condition. For example, if a contest can repeat in different seasons, include the season in both places; otherwise results from separate seasons could be treated as one group.
To show only names, select r.player in the outer query instead of all three example columns. If two tied rows have the same player name, selecting only the name may make the output look duplicated; retain identifying columns if you need to distinguish records. The example assumes comparable score values and a contest key that identifies each group. The source describes SQLite query behavior; it does not establish how other database systems handle bare columns, so check your database’s documentation if you are not using SQLite. [S1]
A practical checklist
Identify the table, group key, value column, and record fields you need.
Group by every column that distinguishes one group from another.
Join the maximum back using both the group key and the maximum value.
Keep identifying fields in the output if tied records could otherwise look alike.
Add
ORDER BYwhen readers need a predictable display order.
A few more useful answers
Why must the join include the group key as well as the maximum value?
Different groups can share the same maximum number. Matching on the group key and value ensures a row is compared only with its own group’s maximum.
Does the query return one row or several when there is a tie?
It returns every matching original row. A tie at the maximum therefore produces multiple rows for that group.
Can I use this pattern for more than one grouping column?
Yes. Include all grouping columns in the subquery’s GROUP BY and match each of them in the join, along with the value.
Sources
References used for this guide.
- [S1] SQLite SELECT: aggregate queries and maximum-value ties sqlite.org · Checked