Re: nvl question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 30 Jun 2001 13:37:37 -0700
Message-ID: <9hldah01eo0@drn.newsguy.com>

In article <Pine.LNX.4.33.0106302204060.4294-100000_at_thenut.eti.pg.gda.pl>, <kazelot_at_thenut.eti.pg.gda.pl says...
>
>
>On Sat, 30 Jun 2001, P.J. wrote:
>> Try this
>> select distinct tab1.col1
>> from tab1
>> order by tab1.col1 desc
>
>I'm sorry, I didn't make myself clear.
>
>> > My select returns null as the last value and I would like it to be first?
>
>I meant I want the rows ordered alphabetically AND have null listed first.
>That's why I thought about NLV.
>
>Thank you,
>kazelot
>

If you have oracle8i release 2 (8.1.6) or up, you could:

scott_at_ORA8I.WORLD> select ename, comm from emp order by comm NULLS FIRST;

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

TURNER              0
ALLEN             300
WARD              500
MARTIN           1400

14 rows selected.

scott_at_ORA8I.WORLD> select ename, comm from emp order by comm NULLS LAST;

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

TURNER              0
ALLEN             300
WARD              500
MARTIN           1400

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

NULLS first and last was added in 816 in support of analytic functions.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Sat Jun 30 2001 - 15:37:37 CDT