Selecting Max Valued Result From Joined Table

I am trying to select a single entry from the
"process_account_inspections" where the column "timestamp" is the Max
(resulting in the most recent inspection). My code is below, however I
can't find how to select the max timestamp with each row returned. Any
suggestions? Currently it just returns the lowest entry in the table.
I tried to GROUP BY accounts.account_id HAVING MAX(InspectionDate) but
that didn't work.

SELECT
accounts.*,
franchisees.*,
offices.*,
process_account_inspections.`timestamp` AS InspectionDate,
process_account_inspections.inspection_score AS InspectionScore,
process_account_inspections.satisfactory AS Satisfactory
FROM accounts
INNER JOIN franchisees ON accounts.franchisee_id_FK =
franchisees.franchisee_id
INNER JOIN offices ON franchisees.office_id_FK = offices.office_id
LEFT JOIN process_account_inspections ON accounts.account_id =
process_account_inspections.account_id_FK
GROUP BY accounts.account_id
ORDER BY process_account_inspections.`timestamp` ASC

Thank you,
Chuck

Chuck Pearce wrote:
>I am trying to select a single entry from the
> "process_account_inspections" where the column "timestamp" is the Max
> (resulting in the most recent inspection). My code is below, however I
> can't find how to select the max timestamp with each row returned. Any
> suggestions? Currently it just returns the lowest entry in the table.
> I tried to GROUP BY accounts.account_id HAVING MAX(InspectionDate) but
> that didn't work.
>
> SELECT
> accounts.*,
> franchisees.*,
> offices.*,
> process_account_inspections.`timestamp` AS InspectionDate,
> process_account_inspections.inspection_score AS InspectionScore,
> process_account_inspections.satisfactory AS Satisfactory
> FROM accounts
> INNER JOIN franchisees ON accounts.franchisee_id_FK =
> franchisees.franchisee_id
> INNER JOIN offices ON franchisees.office_id_FK = offices.office_id
> LEFT JOIN process_account_inspections ON accounts.account_id =
> process_account_inspections.account_id_FK
> GROUP BY accounts.account_id
> ORDER BY process_account_inspections.`timestamp` ASC
>
> Thank you,
> Chuck

Wow, it's been a little while since this question has popped up. At least a
few weeks!

http://dev.mysql.com/doc/refman/5.0/...group-row.html