SQL on Oracle: Already joined two tables, now i want another column from another table(another join)

Question

I have three tables; 1. Students - id, name 2. Subjects - sid,sname 3. Results - id,sid,marks (id and sid are foreign keys referenced by the two tables above)

Now, i perform

SELECT s.sname AS SubjectName, MAX(r.marks) AS MaxMarks
FROM subjects s, results r
WHERE s.sid=r.sid
GROUP BY r.sid, s.sname
ORDER BY r.sid

and i get The Subject Name with the maximum marks scored in them. Now further, i also want the student name that has scored these max marks.

So i tried adding the column r.id, didn't work. I tried adding the table students in this query. I'm probably goofing up with the grouping after adding the table or something?

I did this

SELECT r.id AS StudentID, s.sname AS SubjectName, MAX(r.marks) AS MaxMarks
FROM subjects s, results r
WHERE s.sid=r.sid
GROUP BY r.sid, s.sname, r.id
ORDER BY r.sid

and i got each StudentID, with repeated subjects and the marks scored. Whereas what i basically want is the student who has scored the highest in each subject.

Answer 1

you may use ROW_NUMBER() to tag the student who marked the highest on each subject

SELECT st.name,
       sj.sname,
       r.marks
  FROM (SELECT id,
               sid,
               marks,
               ROW_NUMBER() OVER (PARTITION BY sid 
                                      ORDER BY marks DESC) maxmarks
          FROM results) r
  JOIN students st
    ON r.id = st.id
  JOIN subjects sj
    ON r.sid = sj.sid
 WHERE r.maxmarks = 1