oracle dems作业,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