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...