Write a query to display the name(s) of the students who have secured the maximum marks in each subject, ordered by subject name in ascending order. If there are multiple toppers, display their names in alphabetical order.
Display it as subject_name and student_name.
O/P: first column - subject_name
second column - student_name
Hi, @KenJ, I'm attaching the table definitions below.
can you post the query you are working on? for example
select
Department.department_name,
Student.student_name
from
Student
inner join Department
on Student.department_id = Department.department_id
would list departments and the students within them - not what you are looking for, but just a quick example of joining two tables. Do you have anything similar started?
create table t as (select s.subject_name, min(m.value) as min_mark
from subject s inner join mark m on s.subject_id=m.subject_id
inner join student su
on su.student_id=m.student_id
group by subject_name)
order by subject_name desc;
@KenJ this is all that I tried. But I'm unable to compute the result. Please help me get the required result.
SELECT subject_name,student_name from
Student s inner join Mark m on s.student_id=m.student_id
inner join Subject su on m.subject_id=su.subject_id
inner join (select subject_id,max(value) as maximum from Mark ma group by subject_id) highmarks ON highmarks.subject_id=m.subject_id AND highmarks.maximum=m.value
order by subject_name ASC,student_name ASC;
select subject_name, student_name from student inner join mark using(student_id) inner join subject using(subject_id) where (subject_id, value) in( select subject_id, max(value) from mark inner join subject using(subject_id) group by subject_id ) order by subject_name, student_name;
23 People are following this question.
