Can I use "ORDER BY" clause in a subquery?

sql oracle oracle11g sql-order-by sql-in
sql_dummy picture sql_dummy · Dec 20, 2015 · Viewed 13.6k times · Source 
SELECT * from table1 where column1 IN (SELECT column1 from table1 ORDER BY column1);

Answer

Mureinik picture Mureinik · Dec 20, 2015

You cannot use an order by clause in a query that's used with the in operator. I'm guessing the reason to deny its use is because it would be pointless - an in condition should return true if the left-hand side operand is present in the result of the right hand side query, regardless of its relative position in the result. So allowing you to use an order by clause there would leave Oracle's developers with one of two unappealing choices:

  1. Perform a costly, useless, sorting, or
  2. Silently ignore the order by clause.

Neither of these options fit well to Oracle Database's mindset, so I'm guessing the easiest thing would be to just block this option.

Related questions

How do I do top 1 in Oracle?

How do I do the following? select top 1 Fname from MyTbl In Oracle 11g?

Read More
Display names of all constraints for a table in Oracle SQL

I have defined a name for each of the constraint for the multiple tables that I have created in Oracle SQL. The problem is that to drop a constraint for the column of a particular table I need to know …

Read More
Timestamp conversion in Oracle for YYYY-MM-DD HH:MM:SS format

I'm trying to insert records with YYYY-MM-DD HH:MM:SS format into Oracle from Netezza, but I'm getting invalid date type. How can I accomplish this one? CREATE TABLE AM_PROGRAM_TUNING_EVENT_TMP1 ( ST TIMESTAMP, ET TIMESTAMP, MAS_DIV_…

Read More