Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2003
    Posts
    123

    Cool Unanswered: Trouble with Max query

    I can't seem to get a Max query to work correctly. I want to find the highest (Max) results for each tester. A tester may have multiple tests.

    Tester,Score,TestDate
    Bob,35,20070625
    Bob,42,20070625
    Mary,38,20070624
    Jim,39,20070621
    Jim,43,20070628
    Jim,44,20070628

    I want to return just one row each for Bob, Mary and Jim--their best score:
    Bob,42,20070625
    Mary,38,20070624
    Jim,44,20070628

    But using the query builder, the Max function keeps returning all rows.

    TGIF

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    You need a sub-query to do that correctly.

    The sub query will be

    Code:
    SELECT Tester, Max(Score) As HighestScore FROM YourTableName;
    Save this as maybe qryScoreSub

    The upper level query will look like:

    Code:
    SELECT YourTableName.Tester, qryScoreSub.HighestScore, YourTableName.TestDate FROM YourTableName INNER JOIN qryScoreSub ON YourTableName.Tester = qryScoreSub.Tester And YourTableName.Score = qryScoreSub.HighestScore;
    Save as maybe qryScore.

    HTH,
    Sam

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your subquery needs a GROUP BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by r937
    your subquery needs a GROUP BY
    Oops, you're right, r937. The correct SQL for the sub query is:

    Code:
    SELECT Tester, Max(Score) As HighestScore FROM YourTableName GROUP BY Tester;
    Thanks for pointing it out,

    Sam
    p.s. This won't change the upper query's SQL.

  5. #5
    Join Date
    Aug 2003
    Posts
    123
    Still get multiple rows that way with the same highest score:

    Jim,44,20070624 (actual score was 38)
    Jim,44,20070628 (the real highest score)
    Jim,44,20070629 (actual score was 42)

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jrn0074
    Still get multiple rows ...
    would you mind showing your exact query please? and if it uses a stored query, please show that one too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I suspect the SQL of the subquery wasn't typed correctly, especially since it had an inherent error. Please see Post #4 for the correct SQL for the sub query. Also, make sure the JOIN is correct; there are two junctions necessary (ON........ And...........).

    Sam

  8. #8
    Join Date
    Aug 2003
    Posts
    123
    Here's the table data:

    Tester Score TestDate
    Tom 44 20070623
    Tom 38 20070625
    Tom 45 20070628
    Mary 37 20070624
    Mary 40 20070624
    Bob 41 20070628
    Jim 35 20070614
    Jim 44 20070623

    Here's the first query:
    SELECT tests.Tester, Max(tests.Score) AS HighScore
    FROM tests
    GROUP BY tests.Tester;

    Tester HighScore
    Bob 41
    Jim 44
    Mary 40
    Tom 45

    Here's the second query:
    SELECT tests.Tester, qryScoreSub.HighScore, tests.TestDate
    FROM tests INNER JOIN qryScoreSub ON (tests.Score = qryScoreSub.HighScore) AND (tests.Tester = qryScoreSub.Tester);

    Tester HighScore TestDate
    Tom 45 20070628
    Mary 40 20070624
    Bob 41 20070628
    Jim 44 20070623

    It works fine on this made-up data, but on the actual data I keep getting multiple rows with mixed data. Maybe it's something wrong with the data type on the field(s) being joined that I'll have to research further.

  9. #9
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Try tweaking the upper query's SQL a bit, i.e.

    SELECT tests.Tester, tests.Score, tests.TestDate
    FROM tests INNER JOIN qryScoreSub ON (tests.Tester = qryScoreSub.Tester) AND (tests.Score = qryScoreSub.HighScore);

    I don't know that it'll matter, but it won't hurt.

    Sam

  10. #10
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    I would use the Max and the Last

    cause I want to Highscore (max ) group by the tester and What date was it set


    SELECT Table1.Tester, Max(Table1.Score) AS MaxOfScore, Last(Table1.TestDate) AS LastOfTestDate
    FROM Table1
    GROUP BY Table1.Tester;
    Attached Thumbnails Attached Thumbnails help.JPG  
    Last edited by myle; 06-29-07 at 16:39.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT Tester
         , Score AS MaxOfScore
         , TestDate
      FROM Table1 as T
     WHERE Score =
           ( SELECT Max(Score)
               FROM Table1 
              WHERE Tester = T.Tester )
    look ma, no GROUP BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    cool r937

    what would the speed be sql into a sql

    just asking
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sql into a sql?

    the speed should be no worse, might even be faster
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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