Help With the Groups

[joined]

Hi all,
By now you are all tired of seeing me but maybe some day these problems will help others. The following queries were giveing differnt results and I could not see why.

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


Select A.LoanKeyNum,A.PrinBalAmt, B.NMLLoanInvNum ,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 = 400
Order By B.NMLLoanInvNum



SO I got the advice from angiole to re-writing the first query as follows (look below); However this will cause multiple rows when one of the group by fields is not unique. Should I only group by all the Keys to produce only a single row? Should I group by all the rows where I am using a join? I am thinking that re-writing this query will change the purpose of the query? So basically Doesn't grouping by three rows where originally I grouped by one row change the Logic of the query? Questions Questions. Any help as usual will be appricaited.

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

 

[angiole]

What is it that you want?

Ex: You may recieve your credt statement showing details, or just the balance owing, or the balance owing by merchant, etc, etc.

Once you figure out what your output from the query should be, you'll have a clearer idea of your objective.
AA 8~)