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

maxoracle

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.

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.

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;`

Does this have to be done with MAX or is there another way to do it, if latter then how.

Not sure if I can explain simpler than this.

Best Answer

One way to accomplish what you're looking for is by using the ROWNUM pseudocolumn. Note that you need to define your ORDER BY in an inline view or by some other method to avoid a common trap that people fall into. Here's one way to do it:

SELECT *
FROM
(
    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    TOTAL_SCORE desc
) t
WHERE ROWNUM = 1;

As of Oracle 12c you can use the extended ORDER BY syntax to return just the first row. Here's an example:

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    TOTAL_SCORE desc
FETCH FIRST 1 ROWS ONLY;
Related Question