Solved this problem yesterday after about 45 minutes of experimentation.
Consider the problem of a query against table A that we are pulling records from. Table B is going to be joined and it has related data, but we want the most recent row of B for each A.
(Real life example for those that parse them better: Consider a table of bills, and a table of appraisals that affect those bills - you need to pull the most recent appraisal).
To complicate matters Table C is also going to be joined to table A. We need an aggregate pulled against one of C's columns. the group by clause will be on A's primary key.
(Real life continued - here our third table was 'transactions' and I needed to sum the amount column).
The problem is this - when you do a group by on A you'll only get 1 of B's records. Summing B's information is non-sensical. But you have to have the group by to get the sum of C's column correctly.
My solution - an alias derived table.
A.id AS id,
A.title AS title,
B.appraised AS appraised
SUM(C.amount) AS paid
INNER JOIN C on C.aid = A.id
LEFT JOIN (
SELECT appraised FROM B ORDER BY appraisalDate
) AS B ON B.aid = A.id
GROUP BY A.id
Now the most recent appraisal on table B will be included in the report.
Posted in the hopes someone finds it useful, comments welcome.