Highest Mark in each course with student name

I want to get the highest mark in each course with the title of that course and student name who toke that course exam. and i have these tables , first one students

id	name
1	Jhon Doe
2	Sarah Doe

and Courses table

id	title
1	Math
2	history

and i made a table for connecting students with courses + the mark in course exam

student_id	course_id	exam_mark
1	1	87
1	2	60
2	1	70
2	2	90

so how can i query that.

Answer

You can use window functions. One method is:

select . . .   -- whatever columns you want
from (select sc.*,
             rank() over (partition by course_id order by exam_mark desc) as seqnum
      from student_courses sc
     ) sc join
     students s
     on sc.student_id = s.id join
     courses c
     on sc.course_id = c.id
where seqnum = 1;