order by/first raw

[ocpdude]

I have a table that has different values. one of these values
is 'NULL' value. I need to be able to do order by on this column and still have that NULL value to be my first value all the time.


ex. tablex

name
------
june
july
april
null
may


any idea ????

thanks

[aarroyob]

Hi,

Maybe if you do nvl of of this column and convert it to 11111 if number or aaaaa if character.

Hope this helps

[noor]

Hello,

A possible solution :

select 'column_with_null_value', column2 from your_table
where name is null
union
select name, column2 from your_table
where name is not null
order by name

Hope this helps

Regards

[gpsingh]

ARe u using 8.1.6 and above then u can do this way


sqlplus> set nulls *NULL* (just to highlight null rows , not necessary)

then
use

select name from
order by name asc nulls first ;

the null row should be the first.

[ocpdude]

I've tried this but I got wrong results

sql> set null *null*
sql> select name from tablex
order by name asc/desc nulls first ;


name
-------
april
june
may
*
n
u
l
l
*

any idea ?

thanks

[roger]

what about

select name
from tablex
order by decode(name,null,2,1),name
/

bye

Roger

[gpsingh]

SQL> select * from a;

NAME
----------
GP
GP
GP
*null*
*null*
JP
JP

7 rows selected.

SQL> select * from a order by name asc nulls first;

NAME
----------
*null*
*null*
GP
GP
GP
JP
JP

7 rows selected.


select name from a order by name asc nulls first;

NAME
----------
*null*
*null*
GP
GP
GP
JP
JP