Return all rows with the "MAX" value in SQL?

Question

I have the following table:

Table: Scores What I have:

+----+-------+
| Id | value |
+----+-------+
|  1 |   300 |
|  2 |   300 |
|  3 |   300 |
|  4 |   100 |
|  5 |   200 |
+----+-------+

What I need:

+----+-------+
| Id | value |
+----+-------+
|  1 |   300 |
|  2 |   300 |
|  3 |   300 |
--------------

How would I grab "all" the top scores id 1, 2, 3 in SQL. I started off using MAX (in mysql) but that only returns one row.

Answer 1

SELECT Id, value
    FROM Scores
    WHERE value = (SELECT MAX(value) FROM Scores);

Answer 2

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 FROM
scores
INNER 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 :-)