On Jun 14, 1:18 am, ddf <orat...@msn.com> wrote:
> On Jun 11, 11:29 am, mzebrows <mzebr...@gmail.com> 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?
>
> No. You're making this much harder than it needs to be:
>
> SQL> create table organizations_orders (organization_id number,
> order_id number, modification_date number);
>
> Table created.
>
> SQL>
> SQL> insert all
> 2 into organizations_orders
> 3 values(16, 1, 2001)
> 4 into organizations_orders
> 5 values(15, 1, 2002)
> 6 into organizations_orders
> 7 values(26, 1, 2003)
> 8 into organizations_orders
> 9 values(26, 1, 2004)
> 10 into organizations_orders
> 11 values(18, 1, 2005)
> 12 into organizations_orders
> 13 values(16, 1, 2006)
> 14 into organizations_orders
> 15 values(46, 2, 2001)
> 16 into organizations_orders
> 17 values(23, 2, 2002)
> 18 into organizations_orders
> 19 values(17, 2, 2003)
> 20 into organizations_orders
> 21 values(37, 2, 2004)
> 22 into organizations_orders
> 23 values(56, 3, 2005)
> 24 into organizations_orders
> 25 values(33, 4, 2002)
> 26 select * from dual;
>
> 12 rows created.
>
> SQL>
> SQL> select * from organizations_orders;
>
> ORGANIZATION_ID ORDER_ID MODIFICATION_DATE
> --------------- ---------- -----------------
> 16 1 2001
> 15 1 2002
> 26 1 2003
> 26 1 2004
> 18 1 2005
> 16 1 2006
> 46 2 2001
> 23 2 2002
> 17 2 2003
> 37 2 2004
> 56 3 2005
>
> ORGANIZATION_ID ORDER_ID MODIFICATION_DATE
> --------------- ---------- -----------------
> 33 4 2002
>
> 12 rows selected.
>
> SQL>
> SQL> select order_id, organization_id
> 2 from
> 3 organizations_orders
> 4 where (order_id, modification_date) in (select order_id,
> min(modification_date)
> 5 from organizations_orders
> 6 group by order_id);
>
> ORDER_ID ORGANIZATION_ID
> ---------- ---------------
> 1 16
> 2 46
> 3 56
> 4 33
>
> SQL>
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -
Or you can do this:
SQL> select order_id, organization_id
2 from
3 (select order_id, organization_id,
4 rank() over (partition by order_id order by
modification_date) rk
5 from organizations_orders)
6 where rk=1;
ORDER_ID ORGANIZATION_ID
---------- ---------------
1 16
2 46
3 56
4 33
SQL>
David Fitzjarrell
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
