Students with Highest Mark

Question

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.

What have you tried so far? Why does that not work? – slamballais Jun 14 '21 at 18:27 — slamballais, Jun 14 '21 at 18:27
I had edited my question. give it a look – Ft.Mohak Jun 14 '21 at 18:34 — Ft.Mohak, Jun 14 '21 at 18:34

score 1 · Accepted Answer · answered Jun 14 '21 at 18:39

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;

Students with Highest Mark

1 Answers1