Student who got MAX marks in subject I am facing one issue in getting student name who scored MAX marks in each subject.

Here are my tables below


   

First one is Student table having STUD_ID , STUD_NAME &amp; Second one is MARKS table having STUD_ID, SUBJECT, MARKS.

Can you help me in getting query to retrieve STUD_NAME, SUBJECT, MAX(MARKS).
I having tried below but facing issue in getting STUD_NAME.

select SUBJECT, MAX(MARKS) as Highest
from MARKS inner join STUDENT
ON STUDENT.STUD_ID = MARKS.STUD_ID
GROUP BY SUBJECT
You can use CTE with RANK() function. Read more here: 

Example:

WITH ordermarks AS (
    SELECT [STUD_ID],[SUBJECT],[MARKS], RANK() OVER (PARTITION BY [SUBJECT] ORDER BY [MARKS] DESC) AS rn
    FROM marks
)
SELECT [STUD_NAME],[SUBJECT],[MARKS]
FROM ordermarks om
JOIN Students s ON s.[STUD_ID]=om.[STUD_ID] AND rn=1