SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
05-10-2008, 02:03 PM
| ||||
| ||||
 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      |
|
05-10-2008, 02:03 PM
| ||||
| ||||
| Re: Selecting Max Valued Result From Joined Table 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 |
Linear Mode
