Selecting Third Highest Value SQLServer

Question

I'm trying to return the name & grade of the student with the third highest assessment grade. I'm currently getting the right answer, but I feel like there's a better way to do it.

Dataset

CREATE TABLE Student( Student_ID INT, Student_Name VARCHAR(10) ) INSERT INTO Student (Student_ID, Student_Name) VALUES (1,'Alex'),(2, 'Brett1'),(3,'Cora'),(4,'David'),(5,'Eleanor'),(6,'Brett2')

CREATE TABLE Grades( Student_ID INT, Assignment_ID INT, Grade INT ) INSERT INTO Grades (Student_ID, Assignment_ID, Grade) VALUES (1,10,90),(2,10,85),(3,10,75),(4,10,74),(1,11,80),(2,11,81),(4,11,88),(6,11,86),(2,12,84)

Attempted Solution

SELECT top 1 s.Student_Name, g.Grade FROM Student s INNER JOIN Grades g on s.Student_ID=g.Student_ID WHERE g.Grade < 88 ORDER BY Grade DESC

Without the (WHERE g.Grade<88) it returns the top 3 results, this was a manual way to fix the issue

Thanks in advance~!

Top 1 return the first not the 3rd ..explain better what you need — ScaisEdge, Feb 07 '18 at 20:00

score 2 · Accepted Answer · answered Feb 07 '18 at 20:03

2

for obtain only the 3rd you could use the TOP 1 reverted of the top 3

  select top 1 Student_name, Grade 
  from ( 
    SELECT top 3 s.Student_Name, g.Grade
      FROM Student s
      INNER JOIN Grades g on s.Student_ID=g.Student_ID
      ORDER BY Grade DESC ) t
      order by Grade asc

answered Feb 07 '18 at 20:03

ScaisEdge

131,976
10
91
107

1

This has the advantage that if there only two rows in the inner query it will return the second row. This may or may not be what you desired but it is a subtlety to understand. – Sean Lange Feb 07 '18 at 20:18

jarlh · Answer 2 · 2018-02-07T20:16:56.633

2

"Newer" SQL Server versions (SQL Server 2012+):

SELECT s.Student_Name, g.Grade
FROM Student s
INNER JOIN Grades g on s.Student_ID=g.Student_ID
ORDER BY Grade DESC
OFFSET 2 FETCH FIRST 1 ROW ONLY

edited Feb 07 '18 at 20:16

answered Feb 07 '18 at 20:11

jarlh

42,561
8
45
63

score 0 · Answer 3 · answered Feb 07 '18 at 20:07

0

Use ROW_NUMBER window function

;with cte
AS (
SELECT s.Student_Name, g.Grade ,
RN = row_number()over(order by Grade desc)
FROM Student s
 INNER JOIN Grades g on s.Student_ID=g.Student_ID 
)
Select *
From cte
Where RN = 3

answered Feb 07 '18 at 20:07

Mazhar

3,797
1
12
29

Selecting Third Highest Value SQLServer

3 Answers3