Does Oracle allow an ORDER BY within an IN clause?

Question

Oracle is giving me an error (ORA-00907: missing right parenthesis) when I run this query:

select * 
from reason_for_appointment 
where reason_for_appointment_id in 
(
    select reason_for_appointment_id 
    from appointment_reason 
    where appointment_id = 11 
    order by appointment_reason_id
)

However, when I run just the subquery, there's no error.

Can anyone explain what the problem is?

Answer 1

The inner query results will never be displayed, so theres no point in doing the order by in the nested select. Apply it to the outer query instead.

Answer 2

The problem is that ORDER BY is not permiited inside a subquery like this one. Why did you want to have one?

Answer 3

It looks like you're wanting to display the results from one table using an ordering defined in another table. An inner join should suffice.

select reason_for_appointment.*
from reason_for_appointment rfa, appointment_reason ar
where rfa.reason_for_appointment_id = ar.reason_for_appointment_id
and ar.appointment_id = 11
order by ar.appointment_reason_id;

Answer 4

select * from reason_for_appointment where reason_for_appointment_id in (select reason_for_appointment_id from appointment_reason where appointment_id = 11 order by appointment_reason_id)

try something like: with auxiliar as (select reason_for_appointment_id from appointment_reason where appointment_id = 11 order by appointment_reason_id) select reason_for_appointment_id from appointment_reason where reason_for_appointment_id in (select reason_for_appointment_id from auxiliar)

Answer 5

If your goal is to have the output ordered, you simply want to move the ORDER BY outside of the subquery:

select * from reason_for_appointment where reason_for_appointment_id in
 (select reason_for_appointment_id from appointment_reason where appointment_id = 11)
 order by reason_for_appointment_id

( I'm assuming that where you wrote "appointment_reason_id" you meant "reason_for_appointment_id". If there really are two different columns with these names ... ouch.)