CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Mahmoud Hussien
Mahmoud Hussien

Reputation: 21

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.

Upvotes: 1

Views: 1610

Answers (2)

Gudwlk
Gudwlk

Reputation: 1157

I used RANK() with course_id partitions and exam_marks desc order to get the ranking. then select the ranking = 1 to get the highest value of the ranking. Windows functions are super useful for these type of qestions.

SELECT 
  A.[CourseId]
 ,A.[exam_mark]
 ,A.[name]
 ,A.[title]
FROM
(
     SELECT
      RANK() OVER (PARTITION BY [mark].[course_id]   ORDER BY  [mark].[exam_mark] DESC ) AS [rnk] 
     ,[Courses].[id] AS [CourseId]
     ,[Courses].[title] 
     ,[mark].[exam_mark]
     ,[students_].[name]
   FROM mark
       INNER JOIN  students_ 
             ON  mark.student_id = students_.id
       INNER JOIN  Courses 
             ON  mark.course_id = Courses.id

  ) AS A 
   WHERE A.rnk = 1

Result

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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;

Upvotes: 0

Related Questions