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;
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.