DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: order by/first raw

  1. #1
    Join Date
    Nov 2000
    Posts
    198
    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

  2. #2
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Hi,

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

    Hope this helps

  3. #3
    Join Date
    Oct 2001
    Posts
    83
    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

  4. #4
    Join Date
    Feb 2001
    Posts
    389
    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.



  5. #5
    Join Date
    Nov 2000
    Posts
    198
    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

  6. #6
    Join Date
    Aug 2000
    Posts
    17
    what about

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

    bye

    Roger

  7. #7
    Join Date
    Feb 2001
    Posts
    389
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width