SQL Missing right parenthesis on order by statement

Question

On the order by line, I get an error saying that it's missing a right parenthesis, but I have no idea why.

SELECT LAST_NAME AS "Last Name", DEPARTMENT_ID AS "Department Id", SALARY AS 
Salary
FROM EMPLOYEES JOIN DEPARTMENTS
USING(DEPARTMENT_ID)
WHERE SALARY IN (SELECT MIN(SALARY)
            FROM EMPLOYEES
            GROUP BY DEPARTMENT_ID, SALARY
            ORDER BY DEPARTMENT_ID);

score 5 · Answer 1 · answered Jun 18 '17 at 21:49

5

order by is not allowed in a subquery used on the right-hand side of an in condition. Oracle expected the closing parenthesis before order by.

It makes no sense to order the results of a subquery used in the in conditions.

Besides that, you probably want to group by dept_id only (why also by salary? that makes no sense).

answered Jun 18 '17 at 21:49

Thank you. I wasn't aware you couldn't use order by in an "in" condition – ortz3 Jun 18 '17 at 22:01
@mathguy What's the harm? – Johnny Baloney Jul 04 '18 at 14:28
@JohnnyBaloney - I am not sure what you mean. The obvious answer is that the Oracle syntax doesn't allow it - so "the harm" is that you get an exception. The deeper answer is that the syntax may simply disregard an `ORDER BY` directive there. But if it is not disregarded, then the `ORDER BY` may cause a lot of time to be wasted on an expensive operation (ordering) that has no meaning - the `IN` condition does not depend on the order of the values in the `IN` subquery. – Jul 04 '18 at 15:23

score 3 · Answer 2 · answered Jun 19 '17 at 04:42

The order by clause is not allowed in a subquery

As mathguy has indicated, the order by clause causes the error to be thrown and it does not serve any purpose.

After looking at your query, I suspect you want to do a correlated subquery (use values from an outer query in the inner (sub) query).

Below using the sample schema emp and dept (which looks a lot like your tables), I specifically select the smallest salary for a given department because of the condition, e1.deptno = e.deptno.

Specifically, you are probably concerned with the smallest salary associated with a given department.

SCOTT@dev>SELECT
  2      e.ename,
  3      e.sal,
  4      d.*
  5  FROM
  6      emp e
  7      JOIN dept d ON e.deptno = d.deptno
  8  WHERE
  9          1 = 1
 10      AND
 11          e.sal IN (
 12              SELECT
 13                  MIN(e1.sal)
 14              FROM
 15                  emp e1
 16              WHERE
 17                  e1.deptno = e.deptno
 18          )
 19  ORDER BY d.deptno;

ENAME   SAL   DEPTNO  DNAME       LOC       
MILLER  1300  10      ACCOUNTING  NEW YORK  
SMITH   800   20      RESEARCH    DALLAS    
JAMES   950   30      SALES       CHICAGO

SQL Missing right parenthesis on order by statement

2 Answers2

Linked

Related