Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> 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).Received on Tue Dec 11 2001 - 11:41:19 CST
![]() |
![]() |