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

[Judah McAuley]

As a followup, I wanted to note that I know how to get the current 
status for every record. That query would be

SELECT id
      , name
      , (SELECT status FROM customer_order_status_history
	WHERE id = (SELECT Max(id) FROM customer_order_status_history
		    WHERE id = customer_order.id)) AS order_status
FROM customer_order

This presumes that id's are autoincremental int's (which is true in my 
case).

What I can't figure out is then how to filter this and only return rows 
where order_status would be a certain value.

I tried moving that subselect into the FROM clause and doing an inner 
join on that as a derived table but the WHERE id = customer_order.id 
won't work (for reasons I understand but have difficulty explaining 
succinctly).

So I'm stumped.

Judah

Judah McAuley wrote:
> 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
>