Oracle select query with inner select query error

oracle11g, sql

Solution

The problem is placing `ORDER BY` in the `WHERE` clause subquery. SQL syntax does not allow you to order elements of the subquery in the `WHERE` clause, because it does not change the result of the query overall.

You should move it out to fix the syntax:

SELECT empid  FROM employees WHERE deptid IN
    (
        SELECT deptid FROM department WHERE description LIKE '%application%'
    )
ORDER BY createddate DESC

`createddate` is not a column in `employees` table. It exists only in `department` table

Then you need to join to the `department` table, and use `ORDER BY` on one of its columns:

SELECT e.empid
FROM employees e
JOIN department d ON e.deptid = d.deptid
WHERE d.description LIKE '%application%'
ORDER BY d.createddate DESC

Problem

I am getting a SQL error and trying to resolve, any pointers would be helpful, // this gets executed `SELECT empid FROM employees WHERE deptid IN (10,20,30,40 );` // this gets executed ``` SELECT deptid FROM department WHERE description LIKE '%application%' ORDER BY createddate DESC ``` but the below query throws error: ``` SELECT empid FROM employees WHERE deptid IN (SELECT deptid FROM department WHERE description LIKE '%application%' ORDER BY createddate DESC); ``` error: ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis" Update: 07/14: Updating with the exact solution from @dasblinkenlight: The problem is placing ORDER BY in the WHERE clause subquery. SQL syntax does not allow you to order elements of the subquery in the WHERE clause, because it does not change the result of the query overall This article well explains many of the concepts - http://oraclequirks.blogspot.com/2008/01/ora-00907-missing-right-parenthesis.html "ORA-00907: missing right parenthesis Clearly when one gets a message like this, the first reaction is probably to verify what parenthesis has been left out, but unfortunately there are no missing parentheses at all in this statement. To cut it short, the untold syntax quirk is summarized as follows: don't use ORDER BY inside an IN subquery. Now, one may object that indeed it doesn't make sense to use the ORDER BY inside an IN clause, which is true, because Oracle doesn't care about the row order inside an IN clause:" I tried the SQL statement with WHERE clause and '=' instead of 'IN' and it still threw the error:'`missing right parenthesis`'. conclusion 1 : "Don't use ORDER BY in the WHERE clause subquery" or "Subqueries in the where clause are not allowed to use ORDER BY in Oracle" Conclusion 2 This case-study also shows the scenario where we should go for JOIN rather than select subquery

Original source