Thodoris Thodoris - 6 months ago 16
SQL Question

cant appear only the max date for a number of transactions

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

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.