Re: Ordering of subquery result

Hi,

 you could also re-write your subquery as a join and get it sorted that way.

Karsten

>
> In article <941739399.24425.0.pluto.d4ee154e_at_news.demon.nl>,
> "Sybrand Bakker" <postmaster_at_sybrandb.nospam.demon.nl> wrote:
> > Oracle is a RDBMS where R stands for relational. Relational database
> theory
> > is based on sets. A set is by design unordered. You can't expect a
> subquery
> > ever to provide ordered results. Hence the order by can be only
> implemented
> > in the main query.
> > IMO this also means you can not expect the main query to return rows
> in the
> > order of the subquery, as there is no order.
> > If you really want to return rows in the 'order' you specify, you
> should
> > take the queries apart and handle them by PL/SQL
> >
> > Hth,
> >
> > --
> > Sybrand Bakker, Oracle DBA
> > Brian Rasmusson <br_at_bellesystems.com> wrote in message
> > news:3821B092.501310F3_at_bellesystems.com...
> > > Hi,
> > >
> > > I'm posting this message for a collegue, hope you can help:
> > >
> > > ===========================================================
> > >
> > >
> > > I have two problems. Problem number one:
> > >
> > > I would like to be able to sort a query according to the order of
> the
> > > fields found in a subquery. Example:
> > >
> > > select table1.field1, table1.table2_id
> > > from table1
> > > where table1.table2_id in
> > > (22, 53, 17)
> > > order by <subqueryorder>
> > >
> > > should result in
> > >
> > > field1 table2_id
> > > ------ ---------
> > > Hello 22
> > > My 53
> > > Friend 17
> > >
> > > Of course, you have to imagine that (22, 53, 17) is the result of
a
> > > subquery.
> > >
> > > And if my query was
> > >
> > > select table1.field1, table1.table2_id
> > > from table1
> > > where table1.table2_id in
> > > (53, 17, 22)
> > > order by <subqueryorder>
> > >
> > > It should result in
> > >
> > > field1 table2_id
> > > ------ ---------
> > > My 53
> > > Friend 17
> > > Hello 22
> > >
> > > Again, (53, 17, 22) is the result of a subquery.
> > >
> > > My second problem has to do with ordering the subquery. I have the
> query
> > >
> > > select table1.field1, table1.table2_id
> > > from table1
> > > where table1.table2_id in
> > > (select id
> > > from table2
> > > connect by prior parent_id = id
> > > start with id = &getid
> > > order by level desc)
> > >
> > > but Oracle says ORA-00907: missing right parenthesis unless I
> delete the
> > > order by clause from the subquery. Actually, if I remove the order
> by
> > > clause, everything seems to be working the way I would like it to,
> but
> > > I'm not sure this will continue in future Oracle versions. I'd
> really
> > > like to be able to "hardcode" the order of the subquery, and
making
> sure
> > > that the order of the main query is the same as the order of the
> > > subquery.
> > >
> > > ===========================================================
> > >
> > > Regards,
> > >
> > > Brian Rasmusson
> > > Manager, Software Development
> > > Belle Systems
> > > E-mail: br_at_bellesystems.com
> > > Tel.: +45 5944 2500
> >
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Nov 05 1999 - 09:13:25 CST