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

jason.handby jason.handby at corestar.co.uk
Fri Jun 9 02:34:11 CDT 2006


Hi Judah,


> 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.


Looking at your query above, I'm a bit confused about what the id column
in customer_order_status_history represents. Is it the id of the
customer order (which is what you've implied by comparing it to the
customer_order id in the WHERE clause)? Or is it the id of that
particular status record (which is what you imply by using MAX())?

I'm going to assume that you actually have two id columns in
customer_order_status_history:

	id				id of this status history record
	customer_order_id		id of the order this record
belongs to



In which case, this should work:


SELECT
	customer_order.id, customer_order.name,
	customer_order_status_history.status
FROM
	customer_order
	INNER JOIN customer_order_status_history
		ON customer_order.id =
customer_order_status_history.customer_order_id
WHERE
	customer_order_status_history.id =
		(SELECT MAX(id) FROM customer_order_status_history
		WHERE customer_order_id = customer_order.id)
	AND customer_order_status_history.status = 'Pending'



HTH



Jason



More information about the thelist mailing list