[thelist] SQL: tip on getting most recent child record

[Judah McAuley]

<tip type="SQL" name="getting the most recent child record" 
author="Judah McAuley">

Let's say that you have a table called "order" and a child table called 
"order_status". And orders may have a series of statuses that change 
over time, but only has one current status. One way to deal with that is 
to duplicate the data and store the most recent status in the main order 
table. There is also another way, though, to query it directly out of 
child table. This method presumes auto-increment ids which therefore 
store the more recent status entries as higher valued ids.

order:
id
name

order_status:
id
order_id
name

To get all orders and their most recent status:

SELECT order.id
      , order.name
      , order_status.name AS status
FROM order INNER JOIN order_status
ON order.id = order_status.order_id
AND order_status.id = (SELECT MAX(id) FROM order_status WHERE order_id = 
order.id)

The additional AND clause in the INNER JOIN further restricts the record 
set and the subselect gets the maximum id while also matching against 
the order.id in the current record.

</tip>