Using Max function to return single rows.

beerOne · November 6th, 2008, 09:24 AM

I'm not sure whether this is the right place to ask this question but ....

I have three tables which I need to link together if possible to produce single rows.

In table 1 (PupilUsers) I store each pupil's details identified by UserID as a uniqueidentfier.

In table 2 (CatResults) I store their test results. More than 1 test can be taken but are linked on CatResults.PupilUserID to PupilUser.UserID. Each record includes a column that holds thier test score (QuantativeScore) and a column holding a score or standard nine which falls into one of nine bands (QuantativeSt) depending on the level of thier test score.

In table 3 (CatDiagnostics) I have a column holding a diagnostic explantion of the score (QuantNostic) and a column for a strategy (QuantStrategy) for a teacher to use. Each is identified by standard nine (CatDiagnostics.QuantSt), so there are only 9 rows.

Presently I can link table 1 and table 2 together in a query and using the Max function (Max(QuantativeScore) and Max(QuantativeSt)) to produce single rows showing thier highest score and stanine.

But, when I attempt to link to table 3 to show a diagnostic and strategy based on only their highest scores, I have sets that include multiple rows for each pupil based on all their tests.

This appears to be because there's no direct column mapping between Max(CatResult.QuantativeSt) in table 2 and the stanine score (CatDiagnostics.QuantSt) in table 3.

Is this impossible??

Any help or directions would be very much appreciated

Dave

Old Pedant · November 6th, 2008, 04:52 PM

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):

Code:

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

Code:

    SELECT pupilUserID, MAX(QuantitativeScore), MAX(QuantitySt)
    FROM CatResults
    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.