SQL Missing Right Parenthesis on Order by Statement

ORA-00907 Missing right parenthesis issue - select with order by inside insert query

Both the current answers ignore the fact that using order by and rownum in the same query is inherently dangerous. There is absolutely no guarantee that you will get the data you want. If you want the first row from an ordered query you must use a sub-query:

insert into my_tbl ( col1, col2 )
select data, 'more data'
  from ( select data
           from fir_tabl
          where id = 1
          order by created_on desc )
 where rownum = 1
       ;

You can also use a function like rank to order the data in the method you want, though if you had two created_on dates that were identical you would end up with 2 values with rnk = 1.

insert into my_tbl ( col1, col2 )
select data, 'more data'
  from ( select data
              , rank() over ( order by created_on desc ) as rnk
           from fir_tabl
          where id = 1)
 where rnk = 1
       ;

oracle order by missing right parenthesis

You have incorrect syntax there. The order by clause comes at the end and where comes before group by. So your query becomes:

SELECT ProjName
FROM Project
WHERE ProjId IN
  (SELECT ProjId
  FROM EmpProject
  WHERE rownum<=1
  GROUP BY ProjId
  ORDER BY COUNT(ProjId) DESC
  );

The above isn't probably what you want, assuming you want to get top projId with max count.

In 11g or before, you can use ordering in subquery and then apply the rownum filter outside:

SELECT ProjName
FROM Project
WHERE ProjId IN
  (SELECT ProjId
  FROM
    (SELECT ProjId FROM EmpProject GROUP BY ProjId ORDER BY COUNT(ProjId) DESC
    )
  WHERE rownum<=1
  );

In 12c and above, You can use FETCH FIRST like this:

SELECT ProjName
FROM Project
WHERE ProjId IN
  (SELECT ProjId FROM EmpProject GROUP BY ProjId ORDER BY COUNT(ProjId) DESC
  FETCH FIRST 1 row only
  );

ORDER BY with Inner query, giving ORA-00907 missing right parenthesis

The coding style and unnecessary nested brackets make this really hard to read and interpret. But it also does help that, contrary to some comments, an ORA-00907 doesn't always mean an uneven number of parentheses, it can indicate a more general syntax error that's caused the parser to bail out. In this case it isn't very helpful.

The problem is the order by clause on the penultimate line, within the subquery you're comparing against with in:

...
AND RUN.RN_RUN_ID in(Select max(RUN.RN_RUN_ID) From (((((((RELEASES JOIN RELEASE_CYCLES
...
TEST.TS_TYPE = 'LR-SCENARIO')AND TEST.TS_TEST_ID =145965
ORDER BY TESTCYCL.TC_TESTCYCL_ID)
ORDER BY TESTCYCL.TC_TESTCYCL_ID,STEP.ST_STEP_ORDER

The final ordering is obviously allowed, but in that subquery it is not. So it should end:

...
TEST.TS_TYPE = 'LR-SCENARIO')AND TEST.TS_TEST_ID =145965)
ORDER BY TESTCYCL.TC_TESTCYCL_ID,STEP.ST_STEP_ORDER

I can't test that as I don't have your schema, but a simpler demo might help demonstrate:

select d1.dummy
from dual d1
where d1.dummy in (
  select dummy
  from dual d2
  order by d2.dummy
)
order by d1.dummy;

Error at Command Line : 6 Column : 3
Error report -
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"

Removing the inner order by:

select d1.dummy
from dual d1
where d1.dummy in (
  select dummy
  from dual d2
)
order by d1.dummy;

DUMMY
-----
X     

It is expecting to see a ) instead of that order by, so the error does make some sense, once you know what it wrong; but it doesn't really help you narrow it down.

Incidentally, this is referenced in Oracle support document 731577.1:

Getting ORA-00907: missing right parenthesis when using an ORDER BY clause in a subquery. When the ORDER BY clause is removed the query runs without error.

...

This is expected behavior per Bug 4944718
ORDER BY in a subquery shouldn't work, since the order of the rows is passed to the outer query and has no impact.

It is allowed/ignored in an inline view, but not in a nested subquery. (Though there may be exceptions where it still doesn't throw an error...)

SQL Syntax Error: Missing Right Parenthesis

You have a space in the constraint name, which is probably confusing the syntax parser:

CONSTRAINT equip deptid_fk FOREIGN KEY (deptid) REFERENCES dept (deptid),
                ^

If you need a space in identifiers, delimit them in double-quotes like "equip deptid_fk". But it's easier if you can spell your identifiers without whitespace or punctuation.

missing right parenthesis when creating a view with an order by

Simplify the code, with a derived table you don't have to re-write the TO_CHAR(POST.TANGGAL_POSTING, 'YYYY-MM') part several times:

CREATE VIEW LAPORAN_POSTINGANBULANAN (BULAN_TAHUN, JUMLAH_POSTING) AS
    SELECT "BULAN_TAHUN", COUNT(*) AS "JUMLAHPOSTING"
    FROM
    (
        SELECT TO_CHAR(POST.TANGGAL_POSTING, 'YYYY-MM') AS "BULAN_TAHUN"
        FROM POST
    ) DT
    GROUP BY "BULAN_TAHUN"
    ORDER BY "BULAN_TAHUN" ASC

missing right parenthesis with update statment

Fixed version:

update demo
set    type_id = 28202
where  recid in
       ( select recid from 
                ( select *from demo 
                  order by recid desc )
         where rownum <= 284 )

My first attempt which was wrong (thanks mathguy) was to refactor the outer where rownum ... order by ... by moving it into the inner subquery, however I missed that the outer order by was redundant anyway and it just needed removing.

The 'missing right parenthesis' error was because the parser doesn't recognise the order by in the subquery as this is not allowed (I couldn't see any explicit statement about this in the SQL reference, but it isn't).

---

Related Topics