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