cant appear only the max date for a number of transactions

Question

I execute this query:

SELECT distinct(accounts.account_id), transactions.trn_code , transactions.trn_date

FROM accounts join transactions ON accounts.account_id = transactions.account_id

WHERE accounts.account_currency = 'USD'  AND accounts.account_cards_linked > 1 

AND transactions.trn_date >= '2015/03/01' AND transactions.trn_date <= '2015/03/31'

GROUP BY transactions.trn_code , transactions.trn_date,accounts.account_id

HAVING transactions.trn_date >= MAX(transactions.trn_date)

ORDER BY accounts.account_id;

and I get these results:
click image to see the result grid

My problem is that I want for each one account to appear only the transaction with the latest date. But now if an account has more than one transaction, there are all appeared. (for example, I want the account 912...129 to appears only one time with the latest day,2015/03/05. (see the image for the example))
Any ideas??

Answer 1

You need to do a JOIN on MAX(trn_date) of each account:

SELECT
    a.account_id,
    t.trn_code,
    t.trn_date
FROM accounts a
INNER JOIN transactions t
    ON a.account_id = t.account_id
INNER JOIN (
    SELECT
        account_id, MAX(trn_date) AS trn_date
    FROM transactions
    WHERE
        trn_date >= '2015/03/01' 
        AND trn_date <= '2015/03/31'
    GROUP BY account_id
)td
    ON t.account_id = td.account_id
    AND t.trn_date = td.trn_date
WHERE
    a.account_currency = 'USD'
    AND a.account_cards_linked > 1 
ORDER BY a.account_id

Note: Use meaningful table aliases to improve readability and maintainability.

cant appear only the max date for a number of transactions

Sign up