0
SELECT * from table1 where column1 IN (SELECT column1 from table1 ORDER BY column1);
Lalit Kumar B
  • 43,345
  • 10
  • 82
  • 112
sql_dummy
  • 43
  • 1
  • 1
  • 6
  • Why would you do so? – SMA Dec 20 '15 at 07:06
  • @sebastianForsberg Why is it not possible? – sql_dummy Dec 20 '15 at 07:08
  • @SMA I dont have any requirement but i just want to know why is the query invalid – sql_dummy Dec 20 '15 at 07:09
  • 2
    Because it doesn't make sense. Even if you could order the subquery, how would that change the results of the query? – sstan Dec 20 '15 at 07:12
  • Is possibile but don't make sense. A IN clause scan the entire result set and in this case the order in unuseful.. – scaisEdge Dec 20 '15 at 07:16
  • @sstan I am not worried about whether it makes any sense.I want to know why the query cant execute – sql_dummy Dec 20 '15 at 07:20
  • @scaisEdge I am not worried about whether it makes any sense.I want to know why the query cant execute – sql_dummy Dec 20 '15 at 07:20
  • 2
    SQL is a set-based language and you tell the system *what you want*, not *how to do it*. `IN()` tests whether a value is included in a set of values. *Order* isn't a property of sets. – Damien_The_Unbeliever Dec 20 '15 at 07:25
  • @pedda Why not in your main query? – devpro Dec 20 '15 at 07:26
  • @Damien_The_Unbeliever how about SELECT * from table1 where column1 = (SELECT column1 from table1 ORDER BY column1); where column1 has only single value – sql_dummy Dec 20 '15 at 07:34
  • @PeddaModda Usually you would use ORDER BY in sub-query for **pagination query**. However, the query you have posted makes no sense to use ORDER BY. Have a look at [**How to use ORDER BY in pagination query**](http://stackoverflow.com/questions/30321483/how-rownum-works-in-pagination-query/30321788#30321788) – Lalit Kumar B Dec 20 '15 at 07:51
  • @Pedda: How about *you* come up with a query where having an `order by` clause in the subquery would actually affect the results. If you can't, then there is no value in the question. Oracle may have decided to disallow the `order by` clause simply because it doesn't make sense to do so. – sstan Dec 20 '15 at 07:54
  • @LalitKumarB can you give an example of query using ORDER BY clause in a subquery. – sql_dummy Dec 20 '15 at 07:58
  • @sstan I am not worried about my query all I want to know is if ORDER BY is possible in a subquery in oracle. – sql_dummy Dec 20 '15 at 07:59
  • If your question is just "show me an example of using an `ORDER BY` in a subquery in Oracle", why are we the best place to ask rather than putting some of those words into a search engine? – Damien_The_Unbeliever Dec 20 '15 at 08:09
  • Possible duplicate of [Is order by clause allowed in a subquery](http://stackoverflow.com/questions/2101908/is-order-by-clause-allowed-in-a-subquery) – sstan Dec 20 '15 at 08:15
  • http://dba.stackexchange.com/questions/82930/database-implementations-of-order-by-in-a-subquery – sstan Dec 20 '15 at 08:16
  • @Damien_The_Unbeliever I know, I used search engine but there are multiple answers for multiple cases and most of them are ambigous – sql_dummy Dec 20 '15 at 09:08

3 Answers3

6

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.

Mureinik
  • 252,575
  • 45
  • 248
  • 283
3

Simple and straight answer NO. Why? Purpose of order by is to provide sorting functionality to the resultant data and subquery/inner query data is not the final output rather the partial data which is going to be manipulated further using the outer query and so having an order by in subquery makes no sense at all and illogical altogether. You should rather have the order in your main query like

SELECT * from table1 
where column1 IN (SELECT column1 from table1)
 ORDER BY column1;
Rahul
  • 71,392
  • 13
  • 57
  • 105
1

If the question is can I use ORDER BY, the answer is very straightforward. The Oracle syntax check either accept the ORDER BY or reject it.

In your example

SELECT * from tab1 where col1 IN (SELECT col1 from tab1 ORDER BY col1);

You get an error

ORA-00907: missing right parenthesis

The syntax checker doesn't accept the ORDER BY in the subquery and complaints about missing of the closing parenthesis. That it is syntax check you may see on the fact, that you get the very same error even if the table doesn't exists. (It should be mentioned, that in some cases the allowed syntax is an extension to the standards).

A question should I use ORDER BY is completely different and as pointed out discussed elsewhere.

ADDED EXAMPLES

Here an example where ORDER BY in subquery is allowed

WITH t AS
  (SELECT col1 FROM tab ORDER BY col1
  )
SELECT * FROM t;

In contrary this leads to a syntax error 

WITH t AS
  ( SELECT col1 FROM tab ORDER BY col1
  UNION ALL
  SELECT col1 FROM tab ORDER BY col1
  )
SELECT * FROM t;
Marmite Bomber
  • 14,595
  • 3
  • 21
  • 47