I tried that Peter and for some reason it's still selecting the
oldest date not the newest??? Seems odd? Also I'd really like to
just select id because I'll be using this in a subselect.
Thanks!
Tim
On Dec 8, 2006, at 10:31 AM, Peter Bradley wrote:
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
Be who you are and say what you feel, because those who mind don't
matter and those who matter don't mind.
- Dr. Seuss
