SQL Query to find out the Maximum marks for each subject

Students table contains studentid, subjectid and marks. Where there are 10 subjects and 50 students. what is the Query to find out the Maximum marks obtained in each subject.

Showing Answers 1 - 9 of 9 Answers


  • Jul 30th, 2007

select  s1.student_id, s1.subject_id , s2.mks from students,
(select s.subject_id , max(s.marks) as mks from students s group by s.subject_id)  s2

where s1.subject_id = s2.subject_id and s1.marks = s2.mks
order by s1.student_id


  • Nov 20th, 2007

 select empno,ename,sal from emp where sal in(select max(sal) from emp group by deptno);

  Was this answer useful?  Yes


  • Jan 9th, 2008

Same results but INNER JOIN looks better :)

SELECT     Student.SubjectId, Student.Marks, Student.StudentId
FROM        Student INNER JOIN
                (SELECT  SubjectId, MAX(Marks) AS maxMarks
                 FROM  Student
                 GROUP BY SubjectId) S1
ON S1.SubjectId = Student.SubjectId AND S1.maxMarks = Student.Marks
ORDER BY Student.SubjectId

  Was this answer useful?  Yes

SELECT s1.studentid, s1.subjectid, s2.mks FROM student s1,
(SELECT s.subjectid, max(s.marks) as mks from student s group by s.subjectid ) s2
WHERE s1.subjectid =s2.subjectid and s1.marks= s2.mks
ORDER BY s1.studentid

  Was this answer useful?  Yes

pushpanshu kumar

  • Sep 13th, 2017

Thanks for this...really useful with simple query, got my final output with help of this.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.


Related Answered Questions


Related Open Questions