Oracle SQL order by in subquery problems!

Question

I am trying to run a subquery in Oracle SQL and it will not let me order the subquery columns. Ordering the subquery is important as Oracle seems to choose at will which of the returned columns to return to the main query.

select ps.id, ps.created_date, pst.last_updated, pst.from_state, pst.to_state,
        (select last_updated from mwcrm.process_state_transition subpst
            where subpst.last_updated > pst.last_updated
            and subpst.process_state = ps.id
            and rownum = 1) as next_response
        from mwcrm.process_state ps, mwcrm.process_state_transition pst
        where ps.created_date > sysdate - 1/24
        and ps.id=pst.process_state
        order by ps.id asc

Really should be:

select ps.id, ps.created_date, pst.last_updated, pst.from_state, pst.to_state,
        (select last_updated from mwcrm.process_state_transition subpst
            where subpst.last_updated > pst.last_updated
            and subpst.process_state = ps.id
            and rownum = 1
            order by subpst.last_updated asc) as next_response
        from mwcrm.process_state ps, mwcrm.process_state_transition pst
        where ps.created_date > sysdate - 1/24
        and ps.id=pst.process_state
        order by ps.id asc

Does this answer your question? [Is order by clause allowed in a subquery](https://stackoverflow.com/questions/2101908/is-order-by-clause-allowed-in-a-subquery) — philipxy, Apr 06 '23 at 06:09

score 24 · Answer 1 · answered Feb 25 '11 at 16:12

24

Both dcw and Dems have provided appropriate alternative queries. I just wanted to toss in an explanation of why your query isn't behaving the way you expected it to.

If you have a query that includes a ROWNUM and an ORDER BY, Oracle applies the ROWNUM first and then the ORDER BY. So the query

SELECT *
  FROM emp
 WHERE rownum <= 5
 ORDER BY empno

gets an arbitrary 5 rows from the EMP table and sorts them-- almost certainly not what was intended. If you want to get the "first N" rows using ROWNUM, you would need to nest the query. This query

SELECT *
  FROM (SELECT *
          FROM emp
         ORDER BY empno)
 WHERE rownum <= 5

sorts the rows in the EMP table and returns the first 5.

answered Feb 25 '11 at 16:12

Justin Cave

227,342
24
367
384

Thank you for this excellent answer! I almost went with the first query not realizing my mistake until I saw your post. Did some testing and you're right, the inner query (in second query) returns arbitrary rownum ordering when ORDER BY is applied, but the outer query rownums are in ascending order (allows me to get first row using rownum = 1; a simple beauty over aggregate functions like MAX and MIN). =) – ADTC Sep 28 '12 at 02:48
Thanks for pointing out I wanted to affect my result set and not just be a where subquery – Fering Nov 06 '19 at 22:12

score 22 · Accepted Answer · answered Feb 25 '11 at 15:50

Actually "ordering" only makes sense on the outermost query -- if you order in a subquery, the outer query is permitted to scramble the results at will, so the subquery ordering does essentially nothing.

It looks like you just want to get the minimum last_updated that is greater than pst.last_updated -- its easier when you look at it as the minimum (an aggregate), rather than a first row (which brings about other problems, like what if there are two rows tied for next_response?)

Give this a shot. Fair warning, been a few years since I've had Oracle in front of me, and I'm not used to the subquery-as-a-column syntax; if this blows up I'll make a version with it in the from clause.

select
    ps.id, ps.created_date, pst.last_updated, pst.from_state, pst.to_state,
    (   select min(last_updated)
        from mwcrm.process_state_transition subpst
        where subpst.last_updated > pst.last_updated
          and subpst.process_state = ps.id) as next_response
from <the rest>

+1 for using an aggregate instead of ROWNUM. ROWNUM is doubleplusunrelationalthink. — Tom Anderson, Feb 25 '11 at 15:52
And what if I want to select a different column than the one I'm ordering by? — mamashare, May 23 '19 at 11:41

score 11 · Answer 3 · answered Feb 25 '11 at 15:49

I've experienced this myself and you have to use ROW_NUMBER(), and an extra level of subquery, instead of rownum...

Just showing the new subquery, something like...

(
  SELECT
    last_updated
  FROM
  (
    select
      last_updated,
      ROW_NUMBER() OVER (ORDER BY last_updated ASC) row_id
    from
      mwcrm.process_state_transition subpst
    where
      subpst.last_updated > pst.last_updated
      and subpst.process_state = ps.id
  )
    as ordered_results
  WHERE
    row_id = 1
)
  as next_response

An alternative would be to use MIN instead...

(
  select
    MIN(last_updated)
  from
    mwcrm.process_state_transition subpst
  where
    subpst.last_updated > pst.last_updated
    and subpst.process_state = ps.id
)
  as next_response

plus 1 for using MIN :) (4 years into the future) :) – Alex Jun 18 '15 at 12:48 — Alex, Jun 18 '15 at 12:48

score 1 · Answer 4 · edited Aug 06 '15 at 12:32

The confirmed answer is plain wrong. Consider a subquery that generates a unique row index number. For example ROWNUM in Oracle.

You need the subquery to create the unique record number for paging purposes (see below).

Consider the following example query:

SELECT T0.*, T1.* FROM T0 LEFT JOIN T1 ON T0.Id = T1.Id
JOIN 
(
SELECT DISTINCT T0.*, ROWNUM FROM T0 LEFT JOIN T1 ON T0.Id = T1.Id
WHERE (filter...)
)
WHERE (filter...) AND (ROWNUM > 10 AND ROWNUM < 20)
ORDER BY T1.Name DESC

The inner query is the exact same query but DISTINCT on T0. You can't put the ROWNUM on the outer query since the LEFT JOIN(s) could generate many more results.

If you could order the inner query (T1.Name DESC) the generated ROWNUM in the inner query would match. Since you cannot use an ORDER BY in the subquery the numbers wont match and will be useless.

Thank god for ROW_NUMBER OVER (ORDER BY ...) which fixes this issue. Although not supported by all DB engines.

One of the two methods, LIMIT (does not require ORDER) and the ROW_NUMBER() OVER will cover most DB engines. But still if you don't have one of these options, for example the ROWNUM is your only option then a ORDER BY on the subquery is a must!

@danwyand It was a reaction to your answer "Actually "ordering" only makes sense on the outermost query". Which is not true. That's all. — Erik, Mar 14 '19 at 22:37

Oracle SQL order by in subquery problems!

4 Answers4

Linked