CODEWITHSUNDEEP
sqlsql-server
Dewan Hansen
Dewan Hansen

Reputation: 41

MS-sql retrieve grade data from the oldest students

enter image description here

enter image description here

enter image description here

I'm very new to SQL. I'm trying to retrieve some data from my database. The SQL-database I am using is MS sql and the server has three tables available:

students Grades Grade_types

I am trying to get grade data from the 100 students with the oldest ENROLL_DATE, the ENROLL_DATE is in the table Students.

This is the statement I have tried but it doesn't work: 

 Select top (100) * from students order by ENROLL_DATE ASC inner join grades on students.PERSON_ID = grades.PERSON_ID order by ENROLL_DATE

Upvotes: 0

Views: 90

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Assuming that students can have more than one grade, then you want to select the 100 students first, and then retrieve all the grades for them:

Select *
from (select top (100) s.*
      from students s
      order by ENROLL_DATE ASC
     ) s inner join
     grades g
     on s.PERSON_ID = g.PERSON_ID
order by ENROLL_DATE;

Note that you may want a left join if the older students don't have grades.

Upvotes: 0

SQL Hacks
SQL Hacks

Reputation: 1332

You are very close. There can only be one ORDER BY and it must go at the end:

Select top 100 *
from students inner join grades on students.PERSON_ID = grades.PERSON_ID
order by ENROLL_DATE

Upvotes: 0

Related Questions