Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old November 6th, 2008, 09:24 AM
Registered User
Join Date: Jun 2008
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Using Max function to return single rows.

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


Old November 6th, 2008, 04:52 PM
Friend of Wrox
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts

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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Aggregate Function MAX, SUM Manu SQL Language 2 May 30th, 2006 04:40 AM
select Max 2 rows for each ID veeruu SQL Language 1 January 31st, 2006 05:08 AM
how to return string value with quotes (single or tllcll BOOK: Professional JavaScript for Web Developers ISBN: 978-0-7645-7908-0 3 November 10th, 2005 01:57 PM
Crystal reports max rows in detail section lokilewis Crystal Reports 2 October 6th, 2005 02:47 PM
Problems using the MAX Function Trojan_uk SQL Server ASP 0 November 26th, 2003 11:21 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.