[thelist] SQL: finding records whose most recent status is...

Judah McAuley judah at wiredotter.com
Thu Jun 8 19:31:26 CDT 2006


Situation:
A main order table and then a table that records the history of the 
status of each order. All tables have been simplified to protect the guilty.

customer_order:
id
name

customer_order_status_history:
id
customer_order_id
status

Obviously I can create a column in customer_order that stores the 
current status of an order. That's all well and good but I'm curious 
about the situation where I want to find all the orders whose most 
recent status is a certain value without putting a summary column in the 
main table.

So consider the situation where there are, say, 3 statuses: New, Pending 
and Complete and I want to find all the orders that are currently Pending.

Every order that is Complete will have 3 records in the 
customer_order_status_history and one of them will be Pending. I want to 
exclude these records from the result set.

How would you do this in SQL?

Judah




More information about the thelist mailing list