NEED GROUP BY HELP

[joined]

Hi,

I am running Adaptive Server 11.9 moving towards 12.5.
I have 2 queries that should give me the same results but are not! Query 1 gives me multiple rows of repetitive output which I do NOT want. The Query 2 gives me the correct output however it is not written in the standard ANSI way. I have gotten advice to reformat Query 1 but it still does not work. Thanks for any help which ANYBODY can provide me.


QUERY 1:

Select A.LoanKeyNum,A.PrinBalAmt, B.NMLLoanInvNum ,
max(LoanMsrDte)
From elmo..LoanMeasure A, elmo..Loan B
Where ( A.LoanKeyNum = B.LoanKeyNum )
And B.LoanKeyNum = 5
Group By A.LoanKeyNum
Having ( LoanMsrDte = max(LoanMsrDte) )
Order By B.NMLLoanInvNum
___________________________________________________________
QUERY 1(reformatted):

Select
A.LoanKeyNum,
A.PrinBalAmt,
B.NMLLoanInvNum ,
max(LoanMsrDte)
From
elmo..LoanMeasure A,
elmo..Loan B
Where
A.LoanKeyNum = 400
And A.LoanKeyNum = B.LoanKeyNum
Group By
A.LoanKeyNum,
A.PrinBalAmt,
B.LoanKeyNum
Having
LoanMsrDte = max(LoanMsrDte)
Order By
B.NMLLoanInvNum

______________________________________________________________________________________________________________________

QUERY 2:

Select A.LoanKeyNum,A.PrinBalAmt, B.NMLLoanInvNum ,
max(LoanMsrDte)
From elmo..LoanMeasure A, elmo..Loan B
Where ( A.LoanKeyNum = B.LoanKeyNum )
Group By A.LoanKeyNum
Having ( LoanMsrDte = max(LoanMsrDte) )
And ( A.LoanKeyNum = B.LoanKeyNum )
And B.LoanKeyNum = 5
Order By B.NMLLoanInvNum

 

[JMCraig]

To troubleshoot this, we really need to know more about the relationships between the common key of the two tables (one to many? many to many?)--specifically the one you're using: LoanKeyNum. It's also not entirely clear what the source table is for LoanMsrDte (presumably Loan_Measure?) and how it relates to the key relationships above. One problem that you may be having is the fact that you don't have the GROUP BY in the first instance covering all the non-function columns in the SELECT; this can easily lead to dupes.

However, without a clearer understanding of the nature of the table data with respect to the given key value, I'm not going to be able to help much.

J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net