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