On Jun 13, 11:01 pm, Yogesh <[email protected]> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
No, for two reasons. Reason 1:
SQL> select id,
2 MIN(MODIFICATION_DT) OVER (PARTITION BY MODIFICATION_DT)
AS FIRST_ORGANIZATION
3 from orders join organizations_orders on (id = order_id)
4 /
MIN(MODIFICATION_DT) OVER (PARTITION BY MODIFICATION_DT) AS
FIRST_ORGANIZATION
*
ERROR at line 2:
ORA-00904: "MODIFICATION_DT": invalid identifier
Reason 2 -- he wants the ORGANIZATION_ID, not the MODIFICATION_DATE:
SQL>
SQL> select id,
2 MIN(MODIFICATION_DaTe) OVER (PARTITION BY
MODIFICATION_DaTe) AS FIRST_ORGANIZATION
3 from orders join organizations_orders on (id = order_id)
4 /
ID FIRST_ORGANIZATION
---------- ------------------
2 2001
1 2001
1 2002
4 2002
2 2002
1 2003
2 2003
2 2004
1 2004
3 2005
1 2005
ID FIRST_ORGANIZATION
---------- ------------------
1 2006
12 rows selected.
SQL>
One should always test queries before posting them. This query
works:
SQL> select id, organization_id
2 from
3 (select id, organization_id,
4 rank() over (partition by id order by modification_date)
rk
5 from organizations_orders join orders on (id = order_id))
6 where rk=1;
ID ORGANIZATION_ID
---------- ---------------
1 16
2 46
3 56
4 33
SQL>
The original query posted does not and I can't see how it returns any
data at all:
SQL> select ORDERS.id,
2 (
3 select * from
4 (select dz.ORGANIZATION_id
5 from ORGANIZATIONS_ORDERS dz
6 where dz.ORDER_id = ORDERS.id order by dz.MODIFICATION_DATE)
where
7 rownum=1
8 ) as dzial_2_nazwa
9 from ORDERS ;
where dz.ORDER_id = ORDERS.id order by dz.MODIFICATION_DATE) where
*
ERROR at line 6:
ORA-00904: "ORDERS"."ID": invalid identifier
SQL>
ORDERS.ID is out of scope for that query.
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 [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
