Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Finding row with Max value with inner join and sub-query
TryllZ
post Apr 6 2017, 07:36 PM
Post #1


Newbie
*

Group: Members
Posts: 1
Joined: 6-April 17
Member No.: 51,890



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
Go to the top of the page
 
+Quote Post
burleson
post Apr 7 2017, 10:34 AM
Post #2


Advanced Member
***

Group: Members
Posts: 13,437
Joined: 26-January 04
Member No.: 13



Hi, and welcome to the forum!

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


This is essentially two queries!

Since you need an intermediate table, I would use either a GTT or the WITH clause:

http://www.dba-oracle.com/t_with_clause.htm

http://www.dba-oracle.com/t_sql99_with_clause.htm




--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 22nd June 2017 - 03:23 PM