[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