Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2017
    Posts
    5

    Answered: Finding row with Max value with inner join and sub-query

    I need to SUM the total values of each student using INNER JOIN and then find the row with maximum value.

    I have used the below query to find the SUM of each student and sort in descending order SHOWING ALL the students including the one with the maximum value.

    Code:
    Select      S.Student_ID, S.Student_Name, SUM(A.Score) as "TOTAL_SCORE"
    from        Student S
    inner join  Attempt A
    on          S.Student_ID = A.Student_ID
    group by    S.Student_ID, S.Student_Name
    order by    3 desc;
    I need to ONLY show the student with the maximum value but it shows an empty table.

    Code:
    Select     S.Student_ID, 
               S.Student_Name, 
               MAX(A.Score) as "TOTAL SCORE" 
    from       Student S 
    inner join Attempt A 
    on         S.Student_ID = A.Student_ID 
    where      A.Score = (select   SUM(A.Score) 
                          from     Student S) 
                          group by S.Student_ID, S.Student_Name;
    Can this be done with MAX and SUM in the same command using sub-query or is there another way to do it, if latter then how.

    Not sure if I can explain simpler than this.

    Thank You

  2. Best Answer
    Posted by Littlefoot

    "It is not WHERE, but HAVING clause you need to use. For example:
    Code:
      SELECT s.student_id, s.student_name, SUM (a.score) total_score
        FROM student s INNER JOIN attempt a ON s.student_id = a.student_id
    GROUP BY s.student_id, s.student_name
      HAVING SUM (a.score) = (  SELECT MAX (SUM (a1.score))
                                  FROM attempt a1
                              GROUP BY a1.student_id);
    "


  3. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,105
    Provided Answers: 5
    It is not WHERE, but HAVING clause you need to use. For example:
    Code:
      SELECT s.student_id, s.student_name, SUM (a.score) total_score
        FROM student s INNER JOIN attempt a ON s.student_id = a.student_id
    GROUP BY s.student_id, s.student_name
      HAVING SUM (a.score) = (  SELECT MAX (SUM (a1.score))
                                  FROM attempt a1
                              GROUP BY a1.student_id);

  4. #3
    Join Date
    Mar 2007
    Posts
    625
    Your requirement looks like a simple Top-N query. You may find details about its implementation e.g. in these links:
    http://www.oracle.com/technetwork/is...om-086197.html
    http://www.oracle.com/technetwork/is...om-093877.html
    If you are using 12c: http://www.oracle.com/technetwork/is...m-1999186.html
    (just search for "top-n" or "top- n" in those articles)

  5. #4
    Join Date
    Apr 2017
    Posts
    5
    Quote Originally Posted by Littlefoot View Post
    It is not WHERE, but HAVING clause you need to use. For example:
    Code:
      SELECT s.student_id, s.student_name, SUM (a.score) total_score
        FROM student s INNER JOIN attempt a ON s.student_id = a.student_id
    GROUP BY s.student_id, s.student_name
      HAVING SUM (a.score) = (  SELECT MAX (SUM (a1.score))
                                  FROM attempt a1
                              GROUP BY a1.student_id);
    Thank You Very Much, just as expected.

    I had spent about 4 days on very many forums but was not able to find any proper solution.

    I never received any notification about replies to my question, hence the delay.

Posting Permissions

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