Re: Position of NULLs in a sort

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 08 Apr 1999 02:41:19 GMT
Message-ID: <371015e0.5990844@192.86.155.100>

A copy of this was sent to tony <tony_at_brunnet.net> (if that email address didn't require changing) On Wed, 07 Apr 1999 17:55:29 -0400, you wrote:

>When sorting a column that has some NULL values, the NULL values are
>weighted higher than all other values.
>For example: Descending sort of a number(01) column:
>NULL
>9
>8
>7
>.
>.
>.
>

One way is decode as follows (once without and once with)

SQL> select ename, comm from emp order by comm;

ENAME COMM
---------- ----------

TURNER              0
WARD              500
ALLEN             900
MARTIN           1400

SMITH
JONES
JAMES
MILLER
FORD
ADAMS
BLAKE
CLARK
SCOTT
KING 14 rows selected.

SQL> select ename, comm from emp order by decode( comm, null, -999999999, comm );

ENAME COMM
---------- ----------
SMITH
JONES
CLARK
BLAKE
SCOTT
KING
JAMES
MILLER
FORD
ADAMS

TURNER              0
WARD              500
ALLEN             900
MARTIN           1400

14 rows selected.

Note this will not allow an index to be used to order the data (but if there are NULLs in the data to sort, an index probably is not being used anyway)

>This is counter-intuitive to me, and my client. Is there a parameter
>that can be set to reverse this, or another method? Substituting an
>extremely low value for NULL is an unacceptable solution.
>
>Thanks in advance,
>
>Tony
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Apr 07 1999 - 21:41:19 CDT