ORDER BY With Set Operators

dd · *nix forums beginner Joined: 08 Jun 2005 Posts: 14

typos:
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,col3 from a UNION ALL
select a,b,c from b
order by col1

"dd" <dd@dd.com> ¦b¶l¥ó news:42230cad$1_1@rain.i-cable.com ¤¤¼¶¼g...

dd · *nix forums beginner Joined: 08 Jun 2005 Posts: 14

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?

Google