Maximum mark in Subject with Staff name

Write a query to display list of staff name, subject name handled, maximum mark scored in that subject. Give an alias to the maximum mark as max_mark. Sort the result based on maximum mark in descending.

This is the schema diagram.

This is the code I have tried.

select distinct
       staff_name,
       subject_name,
       max(value) over (partition by sb.staff_id) as max_mark
from   subject sb
       inner join staff st
       on st.staff_id=sb.staff_id
       inner join mark m
       on m.subject_id=sb.subject_id
order by max_mark desc;

I was abled to pass one testcase but I could not abled to pass second testcase and I don’t know what second testcase is and also I am not getting what I have done wrong in my above code.Is there any way of getting solution for this problem.

Answer

It seems that You are looking for the following query:

SELECT
    ST.STAFF_NAME,
    SB.SUBJECT_NAME,
    MAX(M.VALUE) AS MAX_MARK
FROM
    STAFF     ST
    JOIN SUBJECT   SB ON SB.STAFF_ID = ST.STAFF_ID
    JOIN MARK      M ON M.SUBJECT_ID = SB.SUBJECT_ID
GROUP BY
    ST.STAFF_ID,
    ST.STAFF_NAME,
    SB.SUBJECT_ID,
    SB.SUBJECT_NAME
ORDER BY
    MAX_MARK DESC;