Group By Question

[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

 

[r937]

if you are willing to rewrite the queries, i would suggest that you do not use

HAVING LoanMsrDte = max(LoanMsrDte)

as i mentioned in the other thread, there's just no way that the individual values are available in the group

if they are, then sybase is as b0rken as mysql

also, the GROUP BY must include each and every non-aggregate column mentioned in the SELECT list

can you maybe state which rows you want, in english?

that would certainly help me a lot

e.g. loan information for the loan with the latest date

rudy