Stuck in query using in oracle

Question

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):

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

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.

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?

Answer 1

You seem to want a JOIN and window functions:

SELECT sr.*
FROM (SELECT s.*, r.eng, r.maths, r.science,
             ( eng + maths + science ) * 100 / 300 as percentage,
             RANK() OVER (PARTITION BY s.year_id, s.dept_id ORDER BY ( eng + maths + science )) as seqnum
      FROM stu.student s JOIN
           stu.result r
           ON s.stu_id = r.stu_id 
     ) sr
WHERE seqnum = 1
ORDER BY s.year_id, s.dept_id;

Note: This uses RANK() so you can see when multiple students are tied within one department and one year.

This also uses proper, explicit, standard JOIN syntax: Never use commas in the FROM clause.

Answer 2

You can use analytical function as following:

Select id, 
       first_name, 
       year_id, 
       dept_id, 
       total, 
       PER 
     from
       (SELECT T1.id, 
               T1.first_name, 
               T1.year_id, 
               T1.dept_id, 
               T1.total, 
               ( T1.total / 300 ) * 100 PER ,
               Dense_rank() over (partition by year_id, dept_id order by T1.total desc) as rn
        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) T1) where rn = 1;

-- update --

You can achieve it using join and cte with group by as following:

With T1 as
(SELECT S.stu_id  ID, 
        first_name, 
        last_name, 
        year_id, 
        dept_id, 
        eng, 
        maths, 
        science, 
        ( eng + maths + science ) TOTAL 
 FROM   stu.student S, 
        Join stu.result R 
        On (S.stu_id = R.stu_id))
Select S.ID, S.FIRSTNAME, T2.YEAR_ID, T2.DEPT_ID,
       T2.TOTAL, T2.TOTAL/3 AS PER
FROM
(Select year_id, 
       dept_id,
       Max(total) as total
From t1
Group by year_id, 
        dept_id) T2
JOIN T1 ON (T2.TOTAL = S.TOTAL
            AND T2.DEPT_ID = S.DEPT_ID
            AND T2.YEAR_ID = S.YEAR_ID);

Cheers!!