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;

`ORA-00907` is clearly an Oracle error, so why have you tagged [tag:sql-server] and [tag:postgresql]? — Charlieface, Mar 13 '22 at 12:01
why not simple `SELECT f.* FROM file_content f order by fc.last_updated_time ASC`, with offset and fetch https://www.oracletutorial.com/oracle-basics/oracle-fetch/#:~:text=The%20OFFSET%20clause%20specifies%20the%20number%20of%20rows,and%20row%20limiting%20starts%20with%20the%20first%20row. — Jens, Mar 13 '22 at 12:01
I don't see how the above query can cause that error. Also what do you think the `ORDER BY` inside the subquery is doing, why do you think it should be there? — Charlieface, Mar 13 '22 at 12:02
I used Inner query because If I use a single query with both ORDER BY and ROWNUM it is not working correctly. — PrasadM96, Mar 13 '22 at 12:13
PL/SQL is the programming language embedded in the database, it doesn't have a separate release level. Your database is Oracle 11.2.0.3. — William Robertson, Mar 13 '22 at 15:39
Does this answer your question? [How do I limit the number of rows returned by an Oracle query after ordering?](https://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering) — William Robertson, Mar 13 '22 at 15:40

score 0 · Answer 1 · answered Mar 13 '22 at 12:14

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>

SQL error - ORA-00907: missing right parenthesis

1 Answers1