Order by not working in Oracle subquery

Question

I'm trying to return 7 events from a table, from todays date, and have them in date order:

SELECT ID
FROM table
 where ID in (select ID from table
where DATEFIELD >= trunc(sysdate)
order by DATEFIELD ASC)
and rownum <= 7

If I remove the 'order by' it returns the IDs just fine and the query works, but it's not in the right order. Would appreciate any help with this since I can't seem to figure out what I'm doing wrong!

(edit) for clarification, I was using this before, and the order returned was really out:

select ID
from TABLE
where DATEFIELD >= trunc(sysdate)
and rownum <= 7
order by DATEFIELD

Thanks

your sub-select is missing a condition (no `WHERE` but an `AND`). You you please correct the syntax errors? Using an ORDER BY for a sub-select that is used for an `IN` operator makes on sense at all. The order of items for the `IN` is undefined and won't be preserved — , Aug 09 '12 at 11:20
Thank you, I promotly fixed them as soon as I posted (I massively simplified my SQL before posting here) - I only used an in statement because my previous one didn't work, I'll post that one up too. — Nick, Aug 09 '12 at 11:24
See my answer. The rownum can only be applied on an outer query because ordering happens *after* the rownum has been assigned. — , Aug 09 '12 at 11:31

score 10 · Accepted Answer · 2012-08-09T11:33:05.127

The values for the ROWNUM "function" are applied before the ORDER BY is processed. That why it doesn't work the way you used it (See the manual for a similar explanation)

When limiting a query using ROWNUM and an ORDER BY is involved, the ordering must be done in an inner select and the limit must be applied in the outer select:

select *
from (
  select *
  from table
  where datefield >= trunc(sysdate)
  order by datefield ASC
)
where rownum <= 7

Tomek Szpakowicz · Answer 2 · 2018-05-16T11:27:47.990

3

You cannot use order by in where id in (select id from ...) kind of subquery. It wouldn't make sense anyway. This condition only checks if id is in subquery. If it affects the order of output, it's only incidental. With different data query execution plan might be different and output order would be different as well. Use explicit order by at the end of the main query.
It is well known 'feature' of Oracle that rownum doesn't play nice with order by. See http://www.adp-gmbh.ch/ora/sql/examples/first_rows.html for more information. In your case you should use something like:
```
SELECT ID
FROM (select ID, row_number() over (order by DATEFIELD ) r
    from table
    where DATEFIELD >= trunc(sysdate))
WHERE r <= 7
```

See also:

See also other similar questions on SO, eg.:

edited May 16 '18 at 11:27

answered Aug 09 '12 at 11:31

Tomek Szpakowicz

14,063
3
33
55

Many thanks, I got a few great responses, this is probably the best and very interesting reading through your explanation and links. – Nick Aug 09 '12 at 13:29
`order by` in `where id = (select id from ...)` does indeed make a lot of sense in some cases. Think about a process which operates sequentially on a table (e.g. a queue). – dr fu manchu May 15 '18 at 11:03
@drfumanchu Could you elaborate? Provide any links to documentation or examples? SQL commands by definition do not define any processing sequence and for good reasons; RDBMS is free to introduce any processing ordering or concurrency based on available indexes, table statistics, relative cost of evaluation of each expression, cache utilisation, current load etc And queues? You add items to the queue and extract items in the order determined by age or priority, so it is more the queue itself that determines ordering. – Tomek Szpakowicz May 16 '18 at 11:37
1

@TomekSzpakowicz Exactly, you want to extract items from the queue in an order determined by age or priority, so you would use an ORDER BY clause (with FETCH ... ROWS ONLY) to achieve this order, in a subselect of an UPDATE statement (or SELECT ... FOR UPDATE). The actual processing order does not matter, but this is the only way (i know of) to tell the RDBMS "process the 10 oldest entries". MySQL allows ORDER BY and LIMIT in UPDATE statements, if you are used to that, you are quite baffeled when you realize that this is much more complicated in Oracle. – dr fu manchu Jul 13 '18 at 17:04
1

@drfumanchu I see what you mean now. You are right. It makes sense to put a query selecting top n rows from an ordered subquery (as in the selected answer) inside a `where x in (...)` condition. What I meant was it does not make any sense to try to _expose_ the ordering from a subquery contained in `where x in (...)` condition to the containing query, which the question author tried to do and which I have seen many times in code 'out there'. SQL simply does not work like this. – Tomek Szpakowicz Jul 18 '18 at 19:55
@TomekSzpakowicz you are right, exposing the ordering does not make sense – dr fu manchu Sep 14 '18 at 08:54

score 1 · Answer 3 · edited Dec 15 '12 at 13:45

1

Your outer query cant "see" the ORDER in the inner query and in this case the order in the inner doesn't make sense because it (the inner) is only being used to create a subset of data that will be used on the WHERE of the outer one, so the order of this subset doesn't matter.

maybe if you explain better what you want to do, we can help you

edited Dec 15 '12 at 13:45

marc_s

732,580
175
1,330
1,459

answered Aug 09 '12 at 11:28

Diego

34,802
21
91
134

score 0 · Answer 4 · answered Feb 02 '17 at 20:29

ORDER BY CLAUSE IN Subqueries: the order by clause is not allowed inside a subquery, with the exception of the inline views. If attempt to include an ORDER BY clause, you receive an error message

An inline View is a query at the from clause.

SELECT t.* FROM (SELECT id, name FROM student) t

Order by not working in Oracle subquery

4 Answers4