RE: Order By Position and UNION

This works in O7.3.4.5

select * from (
select empno e from emp
union all
select empno e from emp)
order by e;

--
Chris J. Guidry  P.Eng.
ATCO Electric, Metering Services
Phone: (780) 420-4142
Fax: (780) 420-3854
Email: chris.guidry_at_atcoelectric.com



> -----Original Message-----

> From: Larry Elkins [SMTP:elkinsl_at_flash.net]

> Sent: Thursday, July 12, 2001 04:40 PM

> To:   Multiple recipients of list ORACLE-L

> Subject:      OT: Order By Position and UNION

> 

> Listers,

> 

> <<<Start 8.1.7 SQL Manual from tahiti>>>

> 

> For compound queries (containing set operators UNION, INTERSECT, MINUS, or

> UNION ALL), the ORDER BY clause must use positions, rather than explicit

> expressions.

> 

> <<<End 8.1.7 SQL Manual from tahiti>>>

> 

> Now, against an 8.1.7 DB on WIN2K:

> 

> SQL> select deptno, loc from dept

>   2  union

>   3  select empno, ename from emp

>   4  order by deptno --<<<<<< DEPTNO, not 1

>   5  /

> 

>     DEPTNO LOC

> ---------- -------------

>         10 NEW YORK

>         20 DALLAS

>         30 CHICAGO

>         40 BOSTON

>       7369 SMITH

>       7499 ALLEN

> <snip>

> 

> I've always used positional notation in the ORDER BY on my queries using

> set

> operators, and, the snippet from the SQL manual still says you have to.

> The

> 9i manual at tahiti contains the same statement regarding positional

> notation. Now I've got someone telling me they *think* they were using

> expressions even back in 7.x. Now I'm going to play it safe and continue

> to

> use positional notation, but, I was wondering if anyone has any insight

> into

> this. I hadn't tried expressions in V7 and was curious if it was accepted

> even back then. Is this simply a documentation bug? There have been lots

> of

> enhancements to Oracle's SQL over the last few years so I could see them

> missing something.

> 

> Regards,

> 

> Larry G. Elkins

> elkinsl_at_flash.net

> 

> -- 

> Please see the official ORACLE-L FAQ: http://www.orafaq.com

> -- 

> Author: Larry Elkins

>   INET: elkinsl_at_flash.net

> 

> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

> San Diego, California        -- Public Internet access / Mailing Lists

> --------------------------------------------------------------------

> To REMOVE yourself from this mailing list, send an E-Mail message

> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

> the message BODY, include a line containing: UNSUB ORACLE-L

> (or the name of mailing list you want to be removed from).  You may

> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Guidry, Chris
  INET: chris.guidry_at_atcoelectric.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).