Re: how to select NULL first

Edward,
Use DECODE or NVL depending on your allowed null column being selected.

eg. select decode(col2,null,to_date('01/01/0001','mm/dd/yyyy'),col2) from table_a order by col2

This is for col2 where it is a date datatype that can be null.

For a number/varchar2 column use NVL to get a value for the column value which will sorts first. This based on the character set for your installation use. And the data range of values being requested.

hth
Dave fowler

"Edward Rusu" <erusu_at_softcomputer.com> wrote in message news:3af6c051_at_smtp2.softcomputer.com...
> Hi All,
>
> Let's assume that we have a table A, columns c1, c2,.. with index c1. If
 you
> run a select:
>
> select * from A order by c1;
>
> rows with NULL value of c1 will be listed at the end. Is it possible to
> configure Oracle to have NULLs listed first?
>
> We already tried:
>
> select * from A order by 'A'||c1;
>
> but it is not good. It does sort in the right order but sometimes it makes
> Oracle crazy. It incorectly choses executuion path and query takes
> forever.... :-(
>
> Any advise?
>
>
>
> Thanks,
>
> Ed
>
>
>
Received on Mon May 07 2001 - 13:55:02 CDT