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 problem is that ORDER BY is not permiited inside a subquery like this one. Why did you want to have one?