How to make a SQL query for last transaction of every account?

Say I have a table “transactions” that has columns “acct_id” “trans_date” and “trans_type” and I want to filter this table so that I have just the last transaction for each account. Clearly I could do something like

SELECT acct_id, max(trans_date) as trans_date  
FROM transactions GROUP BY acct_id;

but then I lose my trans_type. I could then do a second SQL call with my list of dates and account id’s and get my trans_type back but that feels very cludgy since it means either sending data back and forth to the sql server or it means creating a temporary table.

Is there a way to do this with a single query, hopefully a generic method that would work with mysql, postgres, sql-server, and oracle.

Answer

This is an example of a greatest-n-per-group query. This question comes up several times per week on StackOverflow. In addition to the subquery solutions given by other folks, here’s my preferred solution, which uses no subquery, GROUP BY, or CTE:

SELECT t1.*
FROM transactions t1
LEFT OUTER JOIN transactions t2
  ON (t1.acct_id = t2.acct_id AND t1.trans_date < t2.trans_date)
WHERE t2.acct_id IS NULL;

In other words, return a row such that no other row exists with the same acct_id and a greater trans_date.

This solution assumes that trans_date is unique for a given account, otherwise ties may occur and the query will return all tied rows. But this is true for all the solutions given by other folks too.

I prefer this solution because I most often work on MySQL, which doesn’t optimize GROUP BY very well. So this outer join solution usually proves to be better for performance.