Unanswered: How do I select the most recent account balance per account
I am trying to run a query to select the most recent balance from a table that lists balance by date and account number. Basically I need to select the most recent balance for a given account number. Here are the details:
What works: This gives me the most recent account, but I cannot get the amount
SELECT Max(CBALANCEMIB.BDATE), CBALANCEMIB.ACCOUNTNO
GROUP BY CBALANCEMIB.ACCOUNTNO;
What I want to work: This returns results which should not be in the result set (older balances)
SELECT Max(CBALANCEMIB.BDATE), CBALANCEMIB.ACCOUNTNO, CBALANCE.AMOUNT
GROUP BY CBALANCEMIB.ACCOUNTNO, CBALANCE.AMOUNT;
Am I trying to do something too complicated for the query language?
Thanks for the help. Got it working with some additional fleshing out of the matter.
For reference, here is my final solution.
Pulled the relevant records and account information (PULLACCOUNTS)
SELECT Max(CBALANCEMIB.BDATE) AS MaxOfBDATE, CBALANCEMIB.ACCOUNTNO, ACCOUNTSMIB.ACNAME
FROM CBALANCEMIB LEFT JOIN ACCOUNTSMIB ON ACCOUNTSMIB.ACCOUNTNO = CBALANCEMIB.ACCOUNTNO
GROUP BY CBALANCEMIB.ACCOUNTNO, ACCOUNTSMIB.ACNAME;
Then combined with the balances (COMBINEPULLACCOUNTS)
SELECT PULLACCOUNTS.MAXOFBDATE, PULLACCOUNTS.ACCOUNTNO, CBALANCEMIB.ACCOUNTNO, PULLACCOUNTS.ACNAME, CBALANCEMIB.BDATE, CBALANCEMIB.AMOUNT
FROM CBALANCEMIB, PULLACCOUNTS;
Finally weeded out the unnecessary records by combining with the original accounts pulled
SELECT COMBINEPULLACCOUNT.CBALANCEMIB.ACCOUNTNO, COMBINEPULLACCOUNT.ACNAME, COMBINEPULLACCOUNT.BDATE, COMBINEPULLACCOUNT.AMOUNT
WHERE COMBINEPULLACCOUNT.CBALANCEMIB.ACCOUNTNO = PULLACCOUNTS.ACCOUNTNO AND COMBINEPULLACCOUNT.BDATE=MAXOFBDATE
Sorry about the readability problems of all caps. I didn't do the original DB and the owners seem to favour all caps so I just stuck to their coding standard