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
Answer by prateek ·
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;
Answer by mridultuteja ·
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;