Home » SQL & PL/SQL » SQL & PL/SQL » Why order by clause is not working in subquery
Why order by clause is not working in subquery [message #432072] Fri, 20 November 2009 22:31 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member

i want to update REV_EMP_ID for DETAIL_SEQ = 2; .i want to get immediate higher number of MAIN_SEQ for that DOCUMENT_CODE and EMP_ID ;if i use ORDER BY JC1.MAIN_SEQ then i will get the correct reuslt.but order by clause giving problem



UPDATE EMP_DOUCMENT JC
   SET JC.REV_EMP_ID = (SELECT JC1.DOCUMENT_ID
                          FROM EMP_DOUCMENT JC1
                         WHERE JC1.EMP_ID = '51775'
                           AND JC1.DOCUMENT_CODE = 'DOC-COST'
                           AND JC1.DOCUMENT_ID IS NOT NULL
                           AND ROWNUM = 1
                         ORDER BY JC1.MAIN_SEQ)
 WHERE JC.EMP_ID = '51775'
   AND JC.MAIN_SEQ = '1'
   AND JC.DET_SEQ = 2;
Re: Why order by clause is not working in subquery [message #432076 is a reply to message #432072] Fri, 20 November 2009 23:07 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
ORDER BY columns must exist within SELECT clause

consider using LEAD analytic function
Re: Why order by clause is not working in subquery [message #432077 is a reply to message #432072] Fri, 20 November 2009 23:14 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
As you did not specify, which problem are you exactly facing, I will just describe, what the subquery does.
It fetches rows from EMP_DOUCMENT based on its WHERE conditions. As they contain "ROWNUM = 1", only firstly fetched row is taken. Then it (= that one row) is ordered by MAIN_SEQ.

Have a look at TOP N queries examples, located e.g. on this site here: http://www.orafaq.com/wiki/SQL_FAQ#How_does_one_select_the_TOP_N_rows_from_a_table.3F. The third example (with an ORDER BY clause) is the correct way of implementing it.
Re: Why order by clause is not working in subquery [message #432103 is a reply to message #432072] Sat, 21 November 2009 04:19 Go to previous message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Sub-query is an unordered source of rows.
Sorting of the rows takes a lot of time so you should minimize its usage.
Sorting should be performed in the outer query.

In fact you should be able to use order by in a sub-query - but it might be used only within this sub-query (ex. to get ROWNUM).
You cannot tell what would be the order of data coming from sub-query. Example is a clustered DB where rows are coming from 2 different data sources (ex. UNION ALL) - Oracle might sort the results and return them in sorted groups, ex. (1,4,8,15, 2,3,4,5,10).
Previous Topic: Help me out with this query too
Next Topic: How to add new column in partition table
Goto Forum:
  


Current Time: Wed Sep 28 22:53:22 CDT 2016

Total time taken to generate the page: 0.16302 seconds