Try to search your question here, if you can't find : Ask Any Question Now ?

Stuck in query using in oracle

HomeCategory: stackoverflowStuck in query using in oracle
Avatarsourav asked 1 week ago

I have a table called student which has following rows and columns(year_id,dept_id are the foreign key from another two look up tables):

enter image description here

And I have another table result which holds the id of student(stu_id) and corresponding marks.

enter image description here

So each year has students associated with four different departments.I need to find the topper of each department year wise.So this is what i tried :

SELECT T2.year_id, 
       T2.dept_id, 
       Max(T2.per) PERCENTAGE 
FROM   (SELECT T1.id, 
               T1.first_name, 
               T1.year_id, 
               T1.dept_id, 
               T1.total, 
               ( T1.total / 300 ) * 100 PER 
        FROM   (SELECT S.stu_id                  ID, 
                       first_name, 
                       last_name, 
                       year_id, 
                       dept_id, 
                       eng, 
                       maths, 
                       science, 
                       ( eng + maths + science ) TOTAL 
                FROM   stu.student S, 
                       stu.result R 
                WHERE  S.stu_id = R.stu_id 
                ORDER  BY year_id, 
                          dept_id) T1) T2 
GROUP  BY year_id, 
          dept_id 
ORDER  BY year_id, 
          dept_id; 

So,I got the output of each year each department holding the highest percentage.
enter image description here

But i wanted to also print the name of student who scored highest percentage.So i added group by id but it is not getting me proper result.So,how to bring the name of student in this query?

1 Answers
Best Answer
AvatarMatthias answered 1 week ago
Your Answer

0 + 0 =

Popular Tags

WP Facebook Auto Publish Powered By : XYZScripts.com