Re: select row with greatest valued column - let me try again

[Peter Bradley]

Ysgrifennodd Tim McIntyre:

Thanks for the responses guys however I was running out the door yesterday and I oversimplified my problem. Sorry about that, let me try again.

Here is my "status_histories" table with some data:

id order_id code type created_at 79 1 1 StatusHistoryOrder 2006-12-06 16:46:36 80 1 1 StatusHistoryOrderPayment 2006-12-06 16:46:37 81 1 2 StatusHistoryOrder 2006-12-06 16:46:55 82 1 1 StatusHistoryOrder 2006-12-06 16:47:22 83 1 2 StatusHistoryOrder 2006-12-06 16:47:34 84 2 1 StatusHistoryOrder 2006-12-07 09:50:44 85 2 1 StatusHistoryOrderPayment 2006-12-07 09:50:44 86 2 2 StatusHistoryOrder 2006-12-07 09:50:59 87 2 1 StatusHistoryOrder 2006-12-07 09:52:41 88 2 2 StatusHistoryOrder 2006-12-07 09:54:42 89 3 1 StatusHistoryOrder 2006-12-07 11:57:44 90 3 1 StatusHistoryOrderPayment 2006-12-07 11:57:44 91 4 1 StatusHistoryOrder 2006-12-07 11:57:58 92 4 1 StatusHistoryOrderPayment 2006-12-07 11:57:58

Basically what I'm trying to do is get the most recent "StatusHistoryOrder" for each order_id.

something to the effect of this pseudo sql:

SELECT id FROM status_histories WHERE created_at > all_other_created_ats_in_this_group GROUP BY order_id;

Hope that makes some sense?

SELECT order_id, MAX(created_at)
FROM status_histories
WHERE type = 'StatusHistoryOrder'
GROUP BY order_id

Does that hit the spot?


Peter


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]