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

Judah McAuley judah at wiredotter.com
Fri Jun 9 10:43:03 CDT 2006


jason.handby wrote:
> 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())?

Hi Jason, thanks for chiming in. I think you may have missed my first 
message in which I laid out the table structure, which is:

customer_order:
id
name

customer_order_status_history:
id
customer_order_id
status

I pretty much always use autoincremented integer id's (well, 
technically, numeric 9) as primary keys and then tablename_id as foreign 
keys.

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

I don't believe this should work though as you can't refer to the 
current row's id (WHERE customer_order_id = customer_order.id) in the 
WHERE clause. Unless I'm terribly mistaken. I'll give it a try when I 
get into the office later though.

Thanks for the input.

Judah




More information about the thelist mailing list