# Thread: MAX of a MIN

1. ## MAX of a MIN

OK here's a scenario to illustrate my problem:

Say 5 people took a test and scored the following scores in 3 areas:

Area 1 Scores:
Jay - 4
Bob - 3
Fran - 1
Sam - 2
Bill - 3

Area 2 Scores:
Jay - 3
Bob - 5
Fran - 4
Sam - 2
Bill - 1

Area 3 Scores:
Jay - 3
Bob - 3
Fran - 1
Sam - 4
Bill - 5

I need to find the lowest score for each users across all areas. This I can do by using MIN and GROUP BY User in SQL. This would return me:

Lowest Scores across all areas for each user:
Jay - 3
Bob - 3
Fran - 1
Sam - 2
Bill - 1

Then from this list of lowest scores, I need to find who did the best on their worst scores. In this case it would be Jay and Bob who on their worst score across all areas had a 3.

So basically, I need to do a MAX(MIN(Scores)) which I cannot seem to get SQL to do. I need to return the user and the score. If there is more than one users (like in the scenario above) they all need to be listed along with that score.

3. Sorting would bring the highest scores to the top, but I only want to display the MAX score and their users; the other users and their scores should remain unseen.

4. You're not thinking it through. Here's a rough example of what I mean:

SELECT name, MIN(test_score) AS score
FROM exam
GROUP BY name
ORDER BY score

I hope this is clearer

5. No, I understand what you're saying regarding sorting. Sorting by score would give me this:

Jay - 3
Bob - 3
Sam - 2
Fran - 1
Bill - 1

And I just want the MAX of the group, which would be just this:

Jay - 3
Bob - 3

I just want the best of the worst, so to speak...

6. You could use LIMIT but that would only give you one person even if more than one person with the score.

Another way to do it is to get the value of the score of the first row of your query results and assign it to a varible. Then:
PHP Code:
``` \$high_row = mysql_fetch_array(\$result); \$high_score = \$high_row['score']; mysql_data_seek(\$result, 0); while(\$row = mysql_fetch_array(\$result)){ \$name = \$row['name']; \$score = \$row['score']; if(\$score == \$high_score){ echo "\$name - \$score <br>\n"; }}  ```
Or something like that.

7. I think this should work if MySQL supported SubQueries:

PHP Code:
``` CREATE TABLE temp AS SELECT name, MIN(test_score) AS score  FROM exam  GROUP BY name; SELECT name, score FROM temp WHERE score = (SELECT max(score) FROM temp); DROP TABLE temp;  ```
Maybe a workaround would be to create another temp table and using a join:

PHP Code:
``` CREATE TABLE temp AS SELECT name, MIN(test_score) AS score  FROM exam  GROUP BY name; CREATE table temp1 AS SELECT max(score) FROM temp; SELECT * FROM temp  LEFT JOIN temp1 ON (temp.score = temp1.score); DROP TABLE temp1, temp;  ```

8. Ummm, MySQL doesn't support subqueries.

As for your Plan B, why do three queries when one will do just fine?

9. I would have expected this query to work:

SELECT user, MIN(score) AS minscore
FROM testmaxmin
GROUP BY user
HAVING minscore = MAX(minscore);

But I just tested it on v 3.23.41 and it says

ERROR 1054: Unknown column 'minscore' in 'having clause'

10. Originally posted by freakysid
I would have expected this query to work:

SELECT user, MIN(score) AS minscore
FROM testmaxmin
GROUP BY user
HAVING minscore = MAX(minscore);

But I just tested it on v 3.23.41 and it says

ERROR 1054: Unknown column 'minscore' in 'having clause'

That's because minscore is the VALUE you assigned to score when it was pulled from the db...

Sketch

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•