How do I select the last child row of a parent/child relationship where the last row matches a condition using MySQL

I a question similar to a previous poster.

His question was: "Using MySQL only I would like to select each of the last child rows of a parent child relationship where the child rows are ordered by a timestamp".

I would like to do this, however I would also like to select only child rows where the status is 1.

My tables are fobs_inventory and fobs_inventory_history. I want the newest (ie: most recently timestamped) fobs_inventory_history record for each fobs_inventory, where fobs_inventory_history.status = 1

-------------------------------
| fobs_inventory              |
-------------------------------
| inv_id | other fields       |
-------------------------------
| 1      | ...                |
| 2      | ...                |
| 3      | ...                |
-------------------------------

----------------------------------------------
| fobs_inventory_history                     |
----------------------------------------------
| id | inv_id | status | created_at          |
----------------------------------------------
| 1  | 1      | 0      | 2011-10-11 10:00:00 |
| 2  | 1      | 1      | 2011-08-01 10:00:00 |
| 3  | 1      | 0      | 2011-07-01 10:00:00 |

| 4  | 2      | 1      | 2011-09-11 14:00:00 |
| 5  | 2      | 0      | 2011-08-01 12:00:00 |
| 6  | 2      | 2      | 2011-07-01 00:00:00 |

| 7  | 3      | 1      | 2011-10-11 14:00:00 |
| 8  | 3      | 2      | 2011-08-01 12:00:00 |
| 9  | 3      | 0      | 2011-07-01 00:00:00 |
----------------------------------------------

What is the best SQL syntax to produce a resultset that would look something like the following table?

--------------------------------------------------------------------
| inv_id | fob_inventory_history_id | status | created_at          |
--------------------------------------------------------------------
| 2      | 4                        | 1      | 2011-09-11 14:00:00 |
| 3      | 7                        | 1      | 2011-10-11 14:00:00 |
--------------------------------------------------------------------

I would like to return only those rows for which the most recent timestamp has a status of 1.

edit

After working on this some more, I came up with the solution which i'm posting here as it may help someone else trying to do the same thing.

Essentially the solution was to select all corresponding fields associated with the MAX() and GROUP BY outside of the subselect, check it out:

select h.id, h.fob_id, h.inv_id, h.status, h.created_at from fobs_inventory_history h, (
    select id, inv_id, status, max(created_at) as ts
    from fobs_inventory_history
    group by inv_id
) h2
where h.created_at = h2.ts
and h.inv_id = h2.inv_id 
and h.fob_id = 1 and h开发者_Go百科.status = 1

Thank you so much for posting this!! It solved a problem that I was stuck on for two days. I needed to include some parent information so I added a JOIN to this query like the following:

select i.custid, i.custorderno, h.id, h.fob_id, h.inv_id, h.status, h.created_at from fobs_inventory_history h, (
    select id, inv_id, status, max(created_at) as ts
    from fobs_inventory_history
    group by inv_id
) h2
INNER JOIN invoice AS i ON h2.inv_id = i.inv_id
where h.created_at = h2.ts
and h.inv_id = h2.inv_id 
and h.fob_id = 1 and h.status = 1

Try

select 
   inv_id, id AS fob_inventory_history_id, status, created_at from  fobs_inventory_history 
where
   status = 1
order by created_at desc
LIMIT 2

You can modify the limit based on how many rows you want to fetch