Forum Stats

  • 3,734,036 Users
  • 2,246,863 Discussions
  • 7,857,005 Comments

Discussions

problem with numbers order by

557899
557899 Member Posts: 71
edited July 2008 in SQL & PL/SQL
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
    Boneist Member Posts: 4,983 Gold Trophy
    order by nvl(col, -0.5) ?

    (assuming your column contains integers or nulls)
  • 557899
    557899 Member Posts: 71
    my column contains decimal values also
  • Boneist
    Boneist 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;
  • rajeshmsr
    rajeshmsr 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)
  • Aketi Jyuuzou
    Aketi Jyuuzou 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
  • 557899
    557899 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.