Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Location
    India
    Posts
    109

    Unanswered: MAX() query usage

    Hi all,

    I have a database of marks and I need to find out the maximum mark taken by each student in any subject. For example If am a student and I have got 60 in Language, 70 in Maths, 80 in Science and 90 in Social Studies, I would need the result "90" and the relevant Social Science subject to be displayed so that I know am good at Social Science of all the subjects. Each student may have different subjects.

    The table name is marks. The fields are subject_id, mark_obtained, student_id.

    When I try this query.

    Select subject_id, max(mark_obtained) from marks group by student_id

    In this case, I get the correct maximum mark, but the subject id displayed is incorrect. Can anyone tell me what am doing wrong here?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you may need to consider usign a "group by" clause to uniqeuly identify each student or subject depending on what you are looking for
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2003
    Location
    India
    Posts
    109
    If I give just

    Select max(mark_obtained) from marks group by student_id

    it returns the maximum mark each student scored. How do I find out the subject in which each student had secured this mark?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Select max(mark_obtained) from marks group by student_id, Subject_ID
    OR
    Select max(mark_obtained) from marks group by Subject_ID, student_id
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by healdem
    Select max(mark_obtained) from marks group by student_id, Subject_ID
    OR
    Select max(mark_obtained) from marks group by Subject_ID, student_id
    both of those queries will return a result set that looks like this --

    56
    73
    86
    75
    24
    90
    55
    64

    might be accurate but hardly what i would call useful

    how about this --
    Code:
    select student_id
         , subject_id
         , mark_obtained
      from marks as T
     where mark_obtained =
           ( select max(mark_obtained)
               from marks
              where student_id = T.student_id )
    Edit: fixed error in query
    Last edited by r937; 06-21-06 at 07:55.
    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
  •