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

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.

This works on SQL Server...

SELECT acct_id, trans_date, trans_type
FROM transactions a
WHERE trans_date = (
   SELECT MAX( trans_date )
   FROM transactions b
   WHERE a.acct_id = b.acct_id
)