Question

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 1

Answer:

select subject_name, student_name from Student join Mark using (student_id) join Subject using (subject_id) where (subject_id, value) in (select subject_id, max(value) from Mark group by subject_id) order by subject_name, student_name;