Can I use an "IN (... ORDER BY field)" clause in a subquery?

Question

SELECT * from table1 where column1 IN (SELECT column1 from table1 ORDER BY column1);

@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
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
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
@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

score 6 · Answer 1 · answered Dec 20 '15 at 07:24

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:

Perform a costly, useless, sorting, or
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.

answered Dec 20 '15 at 07:24

Mureinik

297,002
52
306
350

I dont think you are correct. As long as the syntax is fine the query will be executed regardless of the cost. – sql_dummy Dec 20 '15 at 07:37
can you give an example of query using ORDER BY clause in a subquery – sql_dummy Dec 20 '15 at 07:47
1

@sql_dummy, this answer is correct. If you try it you will see that you will get the error `ORA-00907: missing right parenthesis`. – Jeffrey Kemp Dec 30 '15 at 06:48
Hmm. What about when combined with TOP? One may want to ORDER BY a stamp column and then use TOP(10), and *then* target that result with IN. – Jeff McMahan Aug 21 '21 at 15:23

score 3 · Answer 2 · answered Dec 20 '15 at 09:41

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;

Marmite Bomber · Answer 3 · 2015-12-20T10:05:33.783

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;

could you also give an example of ORDER BY in a subquery.(And that subquery not at FROM clause) — sql_dummy, Dec 20 '15 at 09:48

Can I use an "IN (... ORDER BY field)" clause in a subquery?

3 Answers3