Search code examples
sqloracle-databasegreatest-n-per-group

Students with Highest Mark

Write a query to display the student names and the maximum mark scored by them in any subject, ordered by name in ascending order. Give an alias to the maximum mark as MAX_MARK. I am not able to find the logic for this. Kindly help me with it. Do it in oracle SQL I am at beginner level in SQL.

SELECT MAX(M.VALUE), S2.SUBJECT_ID,M.STUDENT_ID, S2.SUBJECT_NAME,S2.SUBJECT_CODE
from Mark M INNER JOIN SUBJECT S2 
ON M.SUBJECT_ID=S2.SUBJECT_ID group BY S2.SUBJECT_ID, 
S2.SUBJECT_CODE, S2.SUBJECT_NAME;

I am getting error with this query if I get this student id with the help of the above query then I can easily solve this question using subquery concept.

SCHEMA IMAGE

Solution

  • You don't need subject there. Question asks Max mark per student, regardless of subject:

    SELECT s.Student_Name, MAX(M.VALUE) as MAX_MARK
from Student s
inner Join Mark M on m.student_id = s.student_id  
group by s.student_id, s.student_name 
order by s.student_name;