Oracle SQL Order by in Subquery Problems!

Oracle SQL order by in subquery problems!

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>

Oracle SQL Subquery Syntax Error caused by ORDER BY

In your first query as correctly said by @Barbaros that ORDER BY is not needed and even rownum = 1 will not do what you are thinking.

Does the following query fulfill your requirements:

SELECT
    E.EMPLOYEE_ID,
    CASE WHEN E.DEPARTMENT_ID IS NOT NULL 
         THEN E.DEPARTMENT_ID
         ELSE LAG(D.DEPARTMENT_ID IGNORE NULLS) OVER(ORDER BY LEVEL)
    END AS DEPARTMENT_ID
FROM
    EMPLOYEE E
    LEFT JOIN DEPARTMENT D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
START WITH E.EMPLOYEE_ID = 19
CONNECT BY E.EMPLOYEE_ID = PRIOR E.MANAGER_ID

Cheers!!

Order by not working in Oracle subquery

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

Get the top row after order by in Oracle Subquery

try this one

select * from
  (SELECT id, name, department, age, score,
  ROW_NUMBER() OVER (partition by department order by age desc, score asc) srlno 
  FROM student) 
where srlno = 1;

I tested in my SQL Developer one case about Subquery in Order By

Yes, it means that if you use a subquery in ORDER BY it must be scalar.

With select * your subquery returns multiple columns and the DBMS would not know which of these to use for the sorting. And if you selected one column only, you would still have to make sure you only select one row of course. (The difference is that Oracle sees the too-many-columns problem immediately, but detect too many rows only when fetching the data.)

This would be allowed:

select * from employees
order by (select birthdate from employees where employee_id = 12345);

This is a scalar query, because it returns only one value (one column, one row). But of course this still makes as little sense as your original query, because the subquery result is independent from the main query, i.e. it returns the same value for every row in the table and thus no sorting takes effect.

A last remark: A subquery in ORDER BY makes very seldomly sense, because that would mean you order by something you don't display. The exception is when looking up a sortkey. E.g.:

select *
from products p
where type = 'shirt' and color = 'blue' and size in ('S', 'M', 'L', 'XL')
order by (select sortkey from sizes s where s.size = p.size);

ORDER BY subquery and ROWNUM goes against relational philosophy?

The ORDER BY in this context is in effect Oracle's proprietary syntax for generating an "ordered" row number on a (logically) unordered set of rows. This is a poorly designed feature in my opinion but the equivalent ISO standard SQL ROW_NUMBER() function (also valid in Oracle) may make it clearer what is happening:

select *
from (
  select ROW_NUMBER() OVER (ORDER BY price) rn, *
  from table
) t
where rn <= 7;

In this example the ORDER BY goes where it more logically belongs: as part of the specification of a derived row number attribute. This is more powerful than Oracle's version because you can specify several different orderings defining different row numbers in the same result. The actual ordering of rows returned by this query is undefined. I believe that's also true in your Oracle-specific version of the query because no guarantee of ordering is made when you use ORDER BY in that way.

It's worth remembering that Oracle is not a Relational DBMS. In common with other SQL DBMSs Oracle departs from the relational model in some fundamental ways. Features like implicit ordering and DISTINCT exist in the product precisely because of the non-relational nature of the SQL model of data and the consequent need to work around keyless tables with duplicate rows.

---

Related Topics