[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