Please check if the following statement can give you desired result.
MIN(MODIFICATION_DT) OVER (PARTITION BY MODIFICATION_DT) ASFIRST_ORGANIZATION --------------- Yogesh On Fri, Jun 11, 2010 at 8:59 PM, mzebrows <[email protected]> wrote: > There's a table ORGANIZATIONS_ORDERS that contains identifiers of > orders, organizations and date of modifications. Every order can have > many organizations. > I need to get list of orders and their first organizations (First > means organization with earliest modification_date) > > ORGANIZATION_ID | ORDER_ID | MODIFICATION_DATE > > ------------------------------------------------------------------------------------ > 16 | 1 | 2001 > 15 | 1 | 2002 > 26 | 1 | 2003 > ....... | ..... | .... > 33 | 4 | 2002 > > So the result for the table above should be > ORDER_ID | ORGANIZATION_ID > --------------------------------------- > 1 | 16 > > But I can't make proper query to get such data. > My query is: > select ORDERS.id, > ( > select * from > (select dz.ORGANIZATION_id > from ORGANIZATIONS_ORDERS dz > where dz.ORDER_id = ORDERS.id order by dz.MODIFICATION_DATE) where > rownum=1 > ) as dzial_2_nazwa > from ORDERS > > But the result of this query is > ORDER_ID | ORGANIZATION_ID > --------------------------------------- > 1 | 33 > And there's the problem because ORDER_ID=1 is not joined with > ORGANIZATION_ID=33. > I mean "where dz.ORDER_id = ORDERS.id " NOT WORKS!!! > > Anyone have idea why? Is this an oracle bug maybe? > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to [email protected] > To unsubscribe from this group, send email to > [email protected] > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
