problem with numbers order by

557899

i have a requirement where i need to sort the records based on the number. I want the output like
-1,null,0,1,2,3 if asc.
3,2,1,0,null,-1 if desc

i am doing
select * from table order by col asc, but the null row is coming either last or first based on asc or desc.

please let me know how can i do it.

Boneist

order by nvl(col, -0.5) ?

(assuming your column contains integers or nulls)

557899

my column contains decimal values also

Boneist

Or this:

with my_tab as (select -1 col from dual union all
                select 0 col from dual union all
                select 1 col from dual union all
                select 2 col from dual union all
                select 3 col from dual union all
                select 0 col from dual union all
                select null col from dual)
-- ********** End of test data setup ***********
select * from my_tab
order by nvl(col, 0) desc, col nulls last; 

with my_tab as (select -1 col from dual union all
                select 0 col from dual union all
                select 1 col from dual union all
                select 2 col from dual union all
                select 3 col from dual union all
                select 0 col from dual union all
                select null col from dual)
-- ********** End of test data setup ***********
select * from my_tab
order by nvl(col, 0), col nulls first;

rajeshmsr

SELECT c FROM
(SELECT c,(CASE
WHEN c IS NULL THEN 1
WHEN c >0 THEN 3
WHEN c<0 THEN 0
WHEN c=0 THEN 2
END) d
FROM
(SELECT 1 c FROM dual
UNION ALL
SELECT 2 c FROM dual
UNION ALL
SELECT NULL c FROM dual
UNION ALL
SELECT 0 c FROM dual
UNION ALL
SELECT NULL c FROM dual
UNION ALL
SELECT -1 c FROM dual
UNION ALL
SELECT -2 c FROM dual)
ORDER BY d ,c)

Aketi Jyuuzou

order by nvl(sign(col),-0.5),col

and

order by nvl(sign(col),-0.5) desc,col desc

557899

thank you every one , my problem got solved simple nvl(col,0) solved my problem it is displaying in the order i wanted.