Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2008
    Posts
    7

    Unanswered: Order by for one query in a Union

    Hi,

    I have a requirement in Oracle query where my query is like

    SELECT 'SELECTALL' , 'Select All' from dual union
    select SUBS_TYPE ,SUBS_DESC from SM_SUBSCRIBER_TYPE

    the above query should return "Select All" as first row and all rows which are retrieved by the second query should be order by upper(SUBS_DESC) so that "Select All" display in first row and remailing rows should display orderby description.

    Any solution plz help me.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Use "UNION ALL" to prevent the re-ordering.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Or, if you use SQL*Plus, you might create additional column to use it in ORDER BY clause; don't display it using the COLUMN SQL*Plus command. Something like this:
    Code:
    SQL> -- note the NOPRINT option
    SQL> column no noprint
    SQL>
    SQL> select 1 no, 'SELECTALL' name, 'Select All' job from dual
      2  union
      3  select 2 no, ename, job from emp
      4  order by no, job;
    
    NAME       JOB
    ---------- ----------
    SELECTALL  Select All
    SCOTT      ANALYST
    FORD       ANALYST
    MILLER     CLERK
    SMITH      CLERK
    ADAMS      CLERK
    JAMES      CLERK
    CLARK      MANAGER
    BLAKE      MANAGER
    JONES      MANAGER
    KING       PRESIDENT
    WARD       SALESMAN
    ALLEN      SALESMAN
    TURNER     SALESMAN
    MARTIN     SALESMAN
    
    15 rows selected.
    
    SQL>

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    write it like this


    Code:
    SELECT 'SELECTALL' , 'Select All' 
    from dual 
    union all
    select subs_type,subs_desc
    from
    (select SUBS_TYPE ,SUBS_DESC 
     from SM_SUBSCRIBER_TYPE
     order by subs_type);
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •