SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Thread: MAX of a MIN

  1. #1
    SitePoint Member
    Join Date
    Feb 2002
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

  2. #2
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How about just adding "ORDER BY score DESC" to your query.

  3. #3
    SitePoint Member
    Join Date
    Feb 2002
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    SitePoint Member
    Join Date
    Feb 2002
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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($result0);
    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. #7
    SitePoint Wizard Chris82's Avatar
    Join Date
    Mar 2002
    Location
    Osnabrück
    Posts
    1,003
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think this should work if MySQL supported SubQueries:

    PHP Code:
    CREATE TABLE temp AS
    SELECT nameMIN(test_score) AS score 
    FROM exam 
    GROUP BY name
    ;

    SELECT namescore FROM temp
    WHERE score 
    = (SELECT max(scoreFROM 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 nameMIN(test_score) AS score 
    FROM exam 
    GROUP BY name
    ;

    CREATE table temp1 AS
    SELECT max(scoreFROM temp;

    SELECT FROM temp 
    LEFT JOIN temp1 ON 
    (temp.score temp1.score);

    DROP TABLE temp1temp

  8. #8
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ummm, MySQL doesn't support subqueries.

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

  9. #9
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #10
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Aaron Brazell
    Technosailor




Bookmarks

Posting Permissions

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