Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606

    Unanswered: Change NULL order by result

    I have some NULL values in a numeric column.. When I ORDER BY nullCol ASC, the NULLs sort 'high' (e.g. they appear last). How can I change it so that the NULLs sort 'low' (e.g. they appear first)?

    It is a foriegn key so I can't set it to 0 or -1.
    Thanks,

    Matt

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    I suppose I could UNION two queries, the first WHERE nullCol IS NULL and then the second WHERE nullColl IS NOT NULL; just wondering if there is another way.
    Thanks,

    Matt

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you'd still have to provide an ORDER BY for the UNION

    select somecolumns
    from yourtable
    order by
    case when numericcolumn is null then 0 else 1 end
    , numericcolumn

    rudy

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Change NULL order by result

    Originally posted by MattR
    I have some NULL values in a numeric column.. When I ORDER BY nullCol ASC, the NULLs sort 'high' (e.g. they appear last). How can I change it so that the NULLs sort 'low' (e.g. they appear first)?

    It is a foriegn key so I can't set it to 0 or -1.
    You can specify where NULLs appear like this:

    SQL> select empno, mgr
    2 from emp order by mgr nulls first;

    EMPNO MGR
    ---------- ----------
    7839
    7788 7566
    7902 7566
    7499 7698
    7521 7698
    7654 7698
    7844 7698
    7900 7698
    7934 7782
    7876 7788
    7566 7839
    7782 7839
    7698 7839
    7369 7902

Posting Permissions

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