Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> ORDER BY With Set Operators

ORDER BY With Set Operators

From: dd <dd_at_dd.com>
Date: Mon, 28 Feb 2005 20:18:52 +0800
Message-ID: <42230cad$1_1@rain.i-cable.com>


In 9iR2, when we use ORDER BY with SET operators(UNION/UNION ALL/INTERSECT/MINUS), the naming rules is very confusing and appreciate if anyone can clarify:

According to SQL Reference:


  1. Must use position notation i.e. ORDER BY 1. We cannot use 'ORDER BY colname'.

By personal testing with 9iR2:


  1. If there is >=2 SET operators, the above rule is true e.g. select col1,col2,col3 from a UNION ALL select a,b,c from b MINUS select i,j,k from c order by 1

 and 'order by colname' => error

2. if there is only 1 SET operator, we can use names instead of position so long as the number and type of the 2 select list columns are matching: select col1, col2 from a UNION ALL
 select a,b,c from b
order by col1 okay

So, why the difference? Which rule should we follow? Received on Mon Feb 28 2005 - 06:18:52 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US