## Forum Stats

• 3,734,036 Users
• 2,246,863 Discussions

Discussions

#### Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

# problem with numbers order by

Member Posts: 71
edited July 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.

• Member Posts: 4,983 Gold Trophy
order by nvl(col, -0.5) ?

(assuming your column contains integers or nulls)
• Member Posts: 71
my column contains decimal values also
• Member Posts: 4,983 Gold Trophy
edited July 2008
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;```
• Member Posts: 144
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)
• Member Posts: 1,072 Bronze Badge
edited July 2008
`order by nvl(sign(col),-0.5),col`
and
`order by nvl(sign(col),-0.5) desc,col desc`
• Member Posts: 71
thank you every one , my problem got solved simple nvl(col,0) solved my problem it is displaying in the order i wanted.
This discussion has been closed.