Re: Ordering of subquery result

Hi,

 I agree with Sybrand.
 You could get what you want if you use dynamic sql, e.g.

 select .... from table
 ...

 order by decode(mycol, 53, 1,
                        65, 2,
                        21, 3,
                            4)

 where the decode part would be generated dynamically based on your subquery. Of course, this has disadvantages (more coding, no compile- time verification, more sga usage due to different-looking queries etc.)

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. Received on Fri Nov 05 1999 - 05:38:24 CST