SQL ORA-00907:缺少右括号

Question

我想按上一次更新的日期按升序顺序订购记录，然后限制记录。当我尝试下面的SQL查询时，出现了一个错误

ORA-00907:缺少右括号

有人能帮我解决这个问题吗？

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

您得到的错误意味着ORDER BY不能用于子查询。见演示：

用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

没有它：

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>