Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

problem with numbers order by

557899Jul 18 2008 — edited Jul 18 2008
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.

Comments

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.
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 15 2008
Added on Jul 18 2008
6 comments
2,265 views