http://jan.kneschke.de/projects/mysql/groupwise-max/ has examples of finding the groupwise maximum without subqueries or multiple queries.