Return all rows with the "MAX" value in SQL?
Use a quick variable:
SELECT @max := max(value) from scores;SELECT id, value FROM scores WHERE value = @max;
or else: (and I am normally in staunch opposition to sub-queries, but this one's a no-brainer.
SELECT id, value FROMscoresINNER JOIN (Select max(value) as value from scores) as max USING(value)
Note that these are both preferable to the more basic `WHERE value = (subquery) because for each of them, the query to find the MAX value is executed exactly once (the absolute guarantee of this is why I prefer the variable-based solution). With the subquery version (in the WHERE, not the JOIN), that query is likely to be executed once per row.
I have done some query analyzing with EXPLAIN EXTENDED
, and the INNER JOIN method is probably the most succinct and optimal of all suggestions (supposing that you are in an environment where using MySQL variables is too cumbersome; I still think it is the cleanest).
performance:
Since some interesting discussion took place, I decided to really dig in and evaluate these things (overkill, I know, but fun and useful knowledge on bigger issues). There is a bit of an analysis trick for detecting full table scans; adding WHERE (@foo := @foo + 1)
to the subqueries in question, then setting @foo to 0, running the query, and seeing what @foo is. It's not the end-all be-all query-toll metric, but it can be quite informative about how often you are asking MySQL to evaluate each row. Here are the "scores" with your sample data (lower is better):
- @ctrahey (both): 5 (scans once to find MAX)
- @Joe Stefanelli: 25 (scans once per row (5*5))
- @Jocelyn : 17 (I can't explain this one, but I would love to learn why :-)