Topic: Selecting Max Valued Result From Joined Table

[ChuckP]

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

[rhodesa]

Just order descending and limit to 1:

Code: [Select]

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` DESC LIMIT 1

[ChuckP]

Ok, I'm going to try to explain it better.


table: inspections
_________________________________________________ _________________
| inspection_id |  inspection_timestamp |  inspection_score  | account_id_FK   |
----------------------------------------------------------------------------
|              1    |            11111111111 |                   8      |                    1  |
----------------------------------------------------------------------------
|              2    |            22222222222 |                   6      |                    1  |
----------------------------------------------------------------------------
|              3    |            11111111111 |                   5      |                    2  |
----------------------------------------------------------------------------


table: accounts

___________________________
| account_id |  account_name | 
-------------------------------
|              1    |  Adam's Auto |
-------------------------------
|              2    |  Bob's Bakery |     
-------------------------------
|              3    |  Carl's Cats    |       
-------------------------------
|              3    |  David's Ducks|       
-------------------------------


I want the records for ALL accounts, matched with their highest inspection score and the correlated inspection date, if available.
If they don't have any inspections then their info should still be in the result set.

Example desired result set for above tables:

3 results:
_________________________________________________ ____________________________________________
| account_id |  account_name | inspection_id |  inspection_timestamp |  inspection_score  | account_id_FK   |
----------------------------------------------------------------------------------------------------------
|              1    |  Adam's Auto |              2    |            22222222222 |                   6      |                    1  |
----------------------------------------------------------------------------------------------------------
|              2    |  Bob's Bakery |              2    |            11111111111 |                   5      |                    2  |     
----------------------------------------------------------------------------------------------------------
|              3    |  Carl's Cats    |           null    |                     null     |                null      |                null   |
----------------------------------------------------------------------------------------------------------
|              4    |  David's Ducks|           null    |                     null     |                null      |                null   |
----------------------------------------------------------------------------------------------------------


Note that while Adam's Auto has two inspections, only the most recent inspection is included in the data set (although it has a lower score).


The closest I've gotten is everything above EXCEPT that the inspection score doesn't match the inspection date, so I get the most recent inspection date with some other date's inspection score, which obviously is worthless.

I've been doing this for hours and I'm going crazy, help please .

Thanks,
chuck Pearce

[fenway]

I didn't read this carefully, but it sounds like the standard groupwise maximum problem... you need to do this in 2 steps.

[rhodesa]

Yeah, as far as I know it would have to be 2 loops:

Code: [Select]

<?php
  $accounts = mysql_query("SELECT * FROM `accounts`");
  while($account = mysql_fetch_assoc($accounts)){
    print_r($account);
    $inspections = mysql_query("SELECT * FROM `inspections` WHERE `account_id_FK` = '{$account['account_id']}' ORDER BY `inspection_score` DESC");
    $inspection = mysql_fetch_assoc($inspections);
    print_r($inspection);
  }
?>

[fenway]

Loops? That would just be a simple join... but I don't see the "MAX" in your code.