Re: PL/SQL challenge

In article <3snvcm$1vm_at_lastactionhero.rs.itd.umich.edu>, ab831_at_detroit.freenet.org writes:

|> Two tables, one key field, one to many relationship.
|> Sample structure:
|>
|> ORDER ORDER_CHANGE
|> ------ -----------
|> ORDER_ID; ORDER_ID
|> CUSTOMER_ID; ORDER_STATUS
|> SHIP_TO_ID: CHANGE_DATE
|> ... GRIEF_CODE
|> ... ...
|>
|> What I need to do is write a little snippet that will
|> do the following:
|> Grab a set of fields from the ORDER table as well as the *first* date where
|> the GRIEF_CODE meets a certain criteria (e.g., is equal to some string) for
|> that particular order in question.
|>
|> Any ideas?

Contrary to an earlier follow-up that suggested using a correlated subquery, I think this is a simple job for GROUP BY:

     select O.order_id, O.customer_id, O.ship_to_id,
            min( OC.change_date )
     from   ORDER O,  ORDER_CHANGE OC
     where  OC.order_id   = O.order_id
       and  OC.grief_code = 'some string'
     group by   O.order_id, O.customer_id, O.ship_to_id ;