Re: Help : Order by with Union clause

From: Kevin McGlinchey <kmcglinc_at_comcat.com>
Date: 1997/08/20
Message-ID: <01bcad0a$3ece0aa0$1140aacc@kevinm>#1/1

Easily - don't refer to the column name. Instead refer to its relative position in the select list. For example:

SQL> select empno, ename
  2 from emp
  3 UNION
  4 select deptno, dname
  5 from dept
  6 order by 1,2
  7
SQL> /     EMPNO ENAME
--------- --------------

       10 ACCOUNTING
       20 RESEARCH
       30 SALES
       40 OPERATIONS
     7369 SMITH
     7499 ALLEN
     7521 WARD
     7566 JONES
     7654 MARTIN
     7698 BLAKE
     7782 CLARK
     7788 SCOTT
     7839 KING
     7844 TURNER
     7876 ADAMS
     7900 JAMES
     7902 FORD
     7934 MILLER

18 rows selected.

SQL> Suman Mehta <sumanp_at_yardi.com> wrote in article <01bcacf7$6ac49920$bfd347cf_at_guest1>...
> Can we write an Order by Clause at the end of a select statement
> having the Union clause in it ?
>
> My SQL query (which has a union clause) works fine without
> it but gives an error message when I try to order by
> one of the columns.
>
> How do we overcome this problem ?
>
>
> ----------------------------------------------
> Suman Mehta
> E-mail ID : sumanp_at_yardi.com
>
Received on Wed Aug 20 1997 - 00:00:00 CDT