Can a Group Query Do What I need to do?

[Len]

I have a table. I need to select the student with the highest test score
from each school.

I am selecting the State for Test purposes!

In the Query below, I get the right test score, but I cannot get the correct
student that the test score matches.

How can this be accomplished?

SELECT FederalClass.ST, Max(FederalClass.TotPotTest) AS MaxOfTotPotTest,
First(FederalClass.StudId) AS FirstOfStudId, Last(FederalClass.ENRL) AS
LastOfENRL, Last("7-A") AS Expr1
FROM FederalClass
GROUP BY FederalClass.ST
ORDER BY FederalClass.ST, Max(FederalClass.TotPotTest);

Do I need to do this in VB?

Thanks in Advance

Len

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Usually the LAST() and FIRST() functions are quite useless because
nobody understands them. Some people think they work the same as the
MIN() and MAX() functions. So I don't know what you are trying to do
with them in your query and I'll ignore them.

You'll also need the School in your query.

Does ST = "State"? I don't know what ENRL (Enroll?) is and will exclude
it and "7-A" since you are only asking for the highest scoring students
per school.

This query assumes that each StudID is unique throughout the state,
e.g., there can be only one StudID 223344 in the state.

(Untested)

SELECT SchoolID, StudID, TotPotTest
FROM FederalClass AS FC
WHERE StudID =
(SELECT StudID
FROM FederalClass
WHERE TotPotTest = (SELECT MAX(TotPotTest) FROM FederalClass
WHERE SchoolID = FC.SchoolID
AND ST = FC.ST)
AND ST = FC.ST)

If there are 2 or more students in the same school w/ the same test
score they will all be selected (ties).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSTgyaYechKqOuFEgEQLrpgCgsA8YqtzKeK78KT0buZSUqASB0z8AoLPb
yW5YDXRr+bOL/4YV3LhpP3lg
=d/1g
-----END PGP SIGNATURE-----

 

[John Spencer]

Try using a sub-query in the FROM clause to identify the St and MaxScore for
each ST. Join your table to the ST and MaxScore

SELECT F.St, F.TotPotTest, F.StudID, F.Enrl, "7-A" as Expr1
FROM FederalClass as F INNER JOIN
(SELECT ST, Max(TotPotTest) as MaxScore
FROM FederalClass
GROUP BY ST) as F2
ON F.St = F2.ST
and F.TotPotTest = F2.MaxScore

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County