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
--
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
