Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    Los Angeles
    Posts
    16

    Unanswered: Very simple aggregate question

    If I have a simple table ("Scores") of students and test scores:

    (First, Score, Hours, Subject)
    John, 85, 2.0, Math
    John, 87, 3.0, History
    John, 67, 3.5, Science

    I can get each students' maximum score but I don't know how to return what subject that highest score was in, or how many hours the highest scoring test took for each student...

    SELECT Scores.First, Max(Scores.Score) AS MaxOfScore, Max(Scores.Hours) AS MaxOfHours, First(Scores.Subject) AS FirstOfSubject
    FROM Scores
    GROUP BY Scores.First;

    ...for example the above query returns highest score but also returns 3.5 hours instead of the 3.0 it took for History, and returns Math instead of History.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    One way is 2 queries. First a totals query that groups on student and returns the max score. The second query joins the table with the first query on both fields, and returns the other data from the table.
    Paul

  3. #3
    Join Date
    Mar 2004
    Location
    Los Angeles
    Posts
    16
    Oh rats! of course I forgot to specify the most important part of the question: can it be done with just ONE query. It seems there should be a trick, as long as I am only specifying 1 field to be aggregated (MAX of score) it logically follows there is a corresponding set of field values (in the same record) for each returned MAX.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select First
         , Score
         , Hours
         , Subject
      from Scores as T
     where Score =
           ( select max(Score)
               from Scores
              where First = T.First )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2004
    Location
    Los Angeles
    Posts
    16
    Thanks pbaldy and r937! I think you are both saying the same thing that there's no way around a nested query (it will require 2 "select" statements) so I will go with the nested query. Thanks again for taking time to respond!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, there is another way
    Code:
    select T.First
         , T.Score
         , T.Hours
         , T.Subject
      from Scores as T
    inner
      join Scores as T2
        on T2.First = T.First
    group
        by T.First
         , T.Score
         , T.Hours
         , T.Subject
    having T.Score = max(T2.Score)
    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
  •