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.
  • Interview Candidate
  • 33
  • 112866

Showing Answers 1 - 33 of 33 Answers

vinodwagh

  • 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

srinu

  • 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

anugrah17

  • 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

chandinikiran

  • Mar 2nd, 2010
 

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

subh

  • Apr 25th, 2020
 

no output display

  Was this answer useful?  Yes

Pendo

  • Jul 17th, 2021
 

SELECT subjectID, MAX(Marks) as Max_Mark FROM Students
GROUP BY subjectID

  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