MySQL JOIN/AGGREGATE function output

Question

I have two tables in my database :

select * from marks;

select * from subjects;

I need to find the id of the students who got the highest marks in each subject along with the subject name, i.e., Resultset should have 3 columns:

student_id	subject_name	maximum_marks
1	PHYSICS	97.5
2	CHEMSITRY	98.5

Please help me write the query for the above result set

This is what I've tried so far

select m.student_id, s.subject_name, max(m.marks) as maximum_marks from marks m inner join subjects s on m.subject_id=s.subject_id group by m.subject_id;

OUTPUT:

Answer 1

I've found a little bit better solution, this is a common use-case of correlated sub-queries, the output can be achieved without a group-by.

select m1.student_id, m1.subject_id, m1.marks, s.subject_name
from marks m1 inner join subjects s
on m1.subject_id=s.subject_id
where m1.marks= 
(select max(marks) from marks m2 where m1.subject_id=m2.subject_id);

Answer 2

SQL Fiddle Demo

select m.student_id, s.subject_name, m.max_marks
from subjects s join (
 select student_id,subject_id, max(marks) as max_marks 
 from marks 
 group by student_id,subject_id
 order by 3 desc
) as m
on s.subject_id = m.subject_id 
group by s.subject_id

Schema & sample & ONLY_FULL_GROUP_BY disabled

CREATE TABLE IF NOT EXISTS `marks` (
  `student_id` int(6) NOT NULL,
  `subject_id` int(6)  NOT NULL,
  `marks` float NOT NULL
) DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `subjects` (
  `subject_id` int(6)  NOT NULL,
  `subject_name` varchar(10)  NOT NULL
) DEFAULT CHARSET=utf8;

INSERT INTO `marks` (`student_id`, `subject_id`, `marks`) VALUES
(1,1,97.5),(1,2,92.5),
(2,1,90.5),(2,2,98.5),
(3,1,90.5),(3,2,67.5),
(4,1,80.5),(4,2,97.5);

INSERT INTO `subjects` (`subject_id`, `subject_name`) VALUES
(2,"Chemistry"),(1,"Physics");