Joining a table with maximum value

Hi. I am trying to join two tables. One table contains user information and the other table contains a series of user transactions. The transaction table has transactions for every user of the site and typically multiple transactions per user. The transaction with the highest txn_id for a given user_id contains their current account balance. I’m trying to join the tables so that the result set includes the user’s information and their account balance. Below is my feeble attempt to do this. Can anyone point me in the right direction? Thanks!

SELECT u.name, u.email,
FROM users AS u
JOIN transactions AS t ON u.id = t.user_id
WHERE t.txn_id = MAX( t.txn_id )

SELECT u.name , u.email , t.balance FROM users AS u INNER JOIN ( SELECT user_id , MAX(txn_id) AS latest FROM transactions GROUP BY user_id ) AS m ON m.user_id = u.id INNER JOIN transactions AS t ON t.user_id = m.user_id AND t.txn_id = m.latest

Worked like a charm, as always. Thanks!

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.