Ordering of subquery result

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 Received on Thu Nov 04 1999 - 10:13:06 CST