SQL Query Help Part 2 - Add filter to joined tables and get max value from filter

Question

I asked this question on SO. However, I wish to extend it further. I would like to find the max value of the 'Reading' column only where the 'state' is of value 'XX' for example.

So if I join the two tables, how do I get the row with max(Reading) value from the result set. Eg.

SELECT s.*, g1.*
FROM Schools AS s
JOIN Grades AS g1 ON g1.id_schools = s.id
WHERE s.state = 'SA' // how do I get row with max(Reading) column from this result set

The table details are:

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

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

Answer 1

One way would be this:

SELECT...
FROM...
WHERE...
AND g1.Reading = (select max(G2.Reading) 
                  from Grades G2 
                  inner join Schools s2 
                  on s2.id = g2.id_schools
                  and s2.state = s.state)

There are certainly more.

Answer 2

I'm guessing [Reading] is some form of numeric value.

SELECT TOP (1)
    s.[Id], 
    s.[State], 
    s.[SchoolName],
    MAX(g.[Reading]) Reading
FROM 
    [Schools] s
    JOIN [Grades] g on g.[id_schools] = s.[Id]
    WHERE s.[State] = 'SA'
Group By 
    s.[Id], 
    s.[State], 
    s.[SchoolName]
Order By 
    MAX(g.[Reading]) DESC

UPDATE:

Looking at Tom's i don't think that would work but here is a modified version that does.

WITH [HighestGrade] (Reading)
AS (
    SELECT
        MAX([Reading]) Reading
    FROM 
        [Grades]
)
SELECT 
    s.*, 
    g.*
FROM 
    [HighestGrade] hg
    JOIN [Grades] AS g ON g.[Reading] = hg.[Reading]
    JOIN [Schools] AS s ON s.[id] = g.[id_schools]
    WHERE s.state = 'SA'

Answer 3

I'd think about using a common table expression:

WITH SchoolsInState (id, state, schoolname)
AS (
    SELECT id, state, schoolname
    FROM Schools
    WHERE state = 'XX'
)
SELECT *
FROM SchoolsInState AS s
JOIN Grades AS g
ON s.id = g.id_schools
WHERE g.Reading = max(g.Reading)

The nice thing about this is that it creates this SchoolsInState pseudo-table which wraps all the logic about filtering by state, leaving you free to write the rest of your query without having to think about it.

Answer 4

This CTE method should give you what you want. I also had it break down by year (grade_year in my code to avoid the reserved word). You should be able to remove that easily enough if you want to. This method also accounts for ties (you'll get both rows back if there is a tie):

;WITH MaxReadingByStateYear AS (
    SELECT
        S.id,
        S.school_name,
        S.state,
        G.grade_year,
        RANK() OVER(PARTITION BY S.state, G.grade_year ORDER BY Reading DESC) AS ranking
    FROM
        dbo.Grades G
    INNER JOIN Schools S ON
        S.id = G.id_schools
)
SELECT
    id,
    state,
    school_name,
    grade_year
FROM
    MaxReadingByStateYear
WHERE
    state = 'AL' AND
    ranking = 1