SQL error - ORA-00907: missing right parenthesis

Question

I want to order records by last updated date in ascending order and then want to limit the records. When I am trying the below SQL query I got an error

ORA-00907: missing right parenthesis

Can someone help me to solve this issue?

SELECT
    f.*
FROM
    file_content f
WHERE
    f.file_id IN (
        SELECT
            fc.file_id
        FROM
            file_content fc
        ORDER BY
            fc.last_updated_time ASC
    )
    AND ROWNUM <= 3;   

Answer 1

Error you got means that ORDER BY can't be used in a subquery. See a demo:

With ORDER BY:

SQL> select *
  2  from dept
  3  where deptno in (select deptno from emp
  4                   order by deptno           --> this
  5                  )
  6    and rownum <= 3;
                 order by deptno           --> this
                 *
ERROR at line 4:
ORA-00907: missing right parenthesis

Without it:

SQL> select *
  2  from dept
  3  where deptno in (select deptno from emp
  4                  )
  5    and rownum <= 3;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO

SQL>