RE: sql query

Steve,

Use the NULLS FIRST option of the ORDER BY (and you probably want to change your UNION to a UNION ALL). Note that you could also include a constant in each select to enforce order, do an order by nvl(col,looow value), etc. Lots of ways, but, the NULLS FIRST option seems clearest to me.

  1 select to_number(null), to_char(null)   2 from dual
  3 union ALL
  4 select empno, ename
  5 from emp
  6* order by 1 nulls first
SQL> / TO_NUMBER(NULL) TO_CHAR(NU
--------------- ----------

           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

15 rows selected.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Steven
> Hovington
> Sent: Tuesday, December 11, 2001 9:45 AM
> To: Multiple recipients of list ORACLE-L
> Subject: sql query
>
>
> Hi,
>
> I have this sql statement:
>
> select
> to_number(null) as id,
> to_char(null) as car_make
> from
> dual
> union
> select
> id,
> car_make
> from
> carmake
> order by
> car_make;
>
> So this selects a blank record and then the records from carmake. But I
> want the blank record to
> appear at the top of the list, and it must be done in the select
> statement.
> Can this be done?
>
> TIA,
>
>
> Thanks,
> Steven Hovington

-- 
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).