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.
