So what happens if the Max(QuantativeScore) and Max(QuantativeSt) don't end up referring to the same record in CatResults?
That may *look* like a theoretical question, but suppose you have TIES?? Or suppose the max score in one test is 100 but in another test it is only 50???
Maybe you have (in CatResults table):
PupilUserId :: QuantitativeScore :: QuantitySt :: other
778 :: 70 (out of 100) :: 6 :: foo
778 :: 72 (out of 100) :: 6 :: bar
778 :: 48 (out of 50) :: 9 :: foobar
SO if you do
SELECT pupilUserID, MAX(QuantitativeScore), MAX(QuantitySt)
GROUP BY pupilUserID
you get the results
778 :: 72 :: 9
which are pretty meaningless.
But even if all the points are out of 100 so toss out that "48" score record, if you wanted to get a value for the "other" field from that record, how would you predict which one you would get, since you might be getting Max(QuantitySt) from the first record while getting Max(QuantitativeScore) from the second record.
Explain how you handle all that, first, please.