Write a query to display the student names and the maximum mark scored by them in any subject

I have tried using group by and getting an error message of single row function cannot return multiple values.

It has three tables to be selected student, subject and mark.

Answer

If you only required student_id and MAX number, you can use only tables Student and Marks as below-

SELECT A.stident_id,MAX(B.Value) max_marks
FROM Student A
INNER JOIN Mark B ON A.Student_id = B.Student_id
GROUP BY A.stident_id

But if you need subject name as well, you can try this below logic-

SELECT AA.stident_id,AA.stident_name,
D.Subject_name,AA.max_marks
FROM
(
    SELECT A.stident_id,A.stident_name,MAX(B.Value) max_marks
    FROM Student A
    INNER JOIN Mark B ON A.Student_id = B.Student_id
    GROUP BY A.stident_id
)AA 
INNER JOIN Marks C ON AA.stident_id = C.stident_id 
AND AA.max_marks = C.Value
INNER JOIN Subject D ON C.subject_id = D.subject_id