Get latest record from multiple for each user

Question

I have beneficiary which have multiple transactions. On home page I want to show the latest transaction created by the customer. So, latest transaction's beneficiary should come on top with amount details. I have created query something like this. But it doesn't show the latest transaction.

$clause = "SELECT b.*, tran.transfer_amount, tran.date_added as tran_date_added
           FROM beneficiary b
           LEFT JOIN customer_beneficiary_mapping cbm ON b.id = cbm.ben_id
           LEFT JOIN
            (SELECT ben_id, transfer_amount, date_added
             FROM transaction
             GROUP BY ben_id
             ) tran
            ON tran.ben_id = b.id
            INNER JOIN system_country_list scl ON scl.country_id = b.ben_country
            AND cbm.cus_id = '$cus_id'
            ORDER BY tran.date_added DESC
            ";

possible duplicate of [How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?](http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql) — Strawberry, Nov 27 '14 at 22:42

score 0 · Answer 1 · answered Nov 27 '14 at 19:47

maybe this works:

SELECT b.*, tran.transfer_amount, tran.date_added as tran_date_added
FROM beneficiary b
LEFT JOIN customer_beneficiary_mapping cbm ON b.id = cbm.ben_id
LEFT JOIN
    (SELECT ben_id, transfer_amount, date_added
     FROM transaction
     GROUP BY ben_id
     ) tran
ON tran.ben_id = b.id
AND tran.date_added = (SELECT MAX(date_added) FROM transaction WHERE ben_id = b.id)
INNER JOIN system_country_list scl ON scl.country_id = b.ben_country
AND cbm.cus_id = '$cus_id'
ORDER BY tran.date_added DESC

Note that i just added a subselect in where clause: AND tran.date_added = (SELECT MAX(date_added) FROM transaction WHERE ben_id = b.id)

score 0 · Answer 2 · answered Nov 27 '14 at 22:35

I found the solution. Rather than deleting the question, I am posting the answer if somebody needs.

$clause = "SELECT b.*, tran.transfer_amount, tran.date_added as tran_date_added
       FROM beneficiary b
       LEFT JOIN customer_beneficiary_mapping cbm ON b.id = cbm.ben_id
       LEFT JOIN
        (SELECT ben_id, transfer_amount, date_added
         FROM transaction
         GROUP BY ben_id
         ORDER BY 1 DESC        <----- Added this line
         ) tran
        ON tran.ben_id = b.id
        INNER JOIN system_country_list scl ON scl.country_id = b.ben_country
        AND cbm.cus_id = '$cus_id'
        ORDER BY tran.date_added DESC
        ";

I still don't know, how come it's working now. I also tried with ORDER BY date_added DESC before. But, it was not working. Anyway, It's working now. :)

There are better answers out there :-( – Strawberry Nov 27 '14 at 22:37 — Strawberry, Nov 27 '14 at 22:37

Get latest record from multiple for each user

2 Answers2