Organizational Research By

Surprising Reserch Topic

oracle sql order by in subquery problems


oracle sql order by in subquery problems  using -'sql,oracle'

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

    
asked Oct 13, 2015 by loknath.ganji
0 votes
3 views



Related Hot Questions



Government Jobs Opening


...