SQL Query Help - Return row in table which relates

问题:

I have two tables:

Table1 = Schools
    Columns: id(PK), state(nvchar(100)), schoolname

Table2 = Grades
    Columns: id(PK), id_schools(FK), Year, Reading, Writing...

I would like to develop a query to find the schoolname which has the highest grade for Reading.

So far I have the following and need help to fill in the blanks:

SELECT Schools.schoolname, Grades.Reading
FROM Schools, Grades
WHERE Schools.id = (* need id_schools for max(Grades.Reading)*)

回答1:

SELECT 
     Schools.schoolname, 
     Grades.Reading 
FROM 
     Schools INNER JOIN Grades on Schools.id = Grades.id_schools
WHERE 
     Grades.Reading = (SELECT MAX(Reading) from Grades)

回答2:

Here's how I solve this sort of problem without using a subquery:

SELECT s.*
FROM Schools AS s
JOIN Grades AS g1 ON g1.id_schools = s.id
LEFT OUTER JOIN Grades AS g2 ON g2.id_schools <> s.id
  AND g1.Reading < g2.Reading
WHERE g2.id_schools IS NULL

Note that you can get more than one row back, if more than one school ties for highest Reading score. In that case, you need to decide how to resolve the tie and build that into the LEFT OUTER JOIN condition.

---

Re your comment: The left outer join looks for a row with a higher grade for the same school, and if none is found, all of g2.* columns will be null. In that case, we know that no grade is higher than the grade in the row g1 points to, which means g1 is the highest grade for that school. It can also be written this way, which is logically the same but might be easier to understand:

SELECT s.*
FROM Schools AS s
JOIN Grades AS g1 ON g1.id_schools = s.id
WHERE NOT EXISTS (
    SELECT * FROM Grades g2 
    WHERE g2.id_schools <> s.id AND g2.Reading > g1.Reading)

You say it's not working. Can you be more specific? What is the answer you expect, and what's actually happening, and how do they differ?

---

edit: Changed = to <> as per suggestion in comment by @potatopeelings. Thanks!

回答3:

This should do it

select * from Schools as s
where s.id=(
select top(1) id_schools from grades as g
order by g.reading desc)