CODEWITHSUNDEEP
sqldatabase
Parvinder Singh
Parvinder Singh

Reputation: 43

Write a query to get name and marks of second highest scorer for every course

Write a query to get name and marks of second highest scorer for every course.

enter image description here

Upvotes: 0

Views: 233

Answers (2)

Coder221
Coder221

Reputation: 1433

This will give you second highest marks

Select
  (SELECT MAX(Marks) FROM Marks
  WHERE Marks NOT IN 
  (SELECT MAX(Marks) FROM Marks)) AS 'Second Highest'
  from Marks

Upvotes: 2

Parvinder Singh
Parvinder Singh

Reputation: 43

WITH MarksCTE AS ( Select DENSE_RANK() OVER (PARTITION BY CourseID ORDER BY Marks desc) as Rank, m.Marks,c.Name as Course,s.Name as Student From Marks as M Inner Join Student as s on S.Id = M.StudentID Inner Join Course as c on c.Id = M.CourseID ) Select Marks,Course,Student from MarksCTE where Rank = 2

Upvotes: 0

Related Questions