Post reply

order by w/ NULL at bottom

  • May 6, 2004 at 1:10 pm

    #111226

    This may be easy but I haven't found how to do this...  I need to return a set of results with product name in alphabetical order.  This currently returns NULL values at the top of the result set.  How can I get my items A-Z, then NULLs?

    Thanks. 

  • May 6, 2004 at 1:15 pm

    #505543

    I typically cheat a bit and use the ISNULL to replace the nulls with all Z's

    SELECT fld

    FROM Foo

    ORDER BY ISNULL(fld,'zzzzzzzz')

     




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • May 6, 2004 at 2:17 pm

    #505552

    ORDER BY ISNULL(fld,'zzzzzzzz')

    This only works thou if you know there will be no value over 'zzzzzzzz' such as 'zzzzzzzzz'.

    To always be sure I get my nulls at the end I do like so.

    ORDER BY (CASE WHEN fld IS NULL THEN 1 ELSE 0 END) ASC

    So no matter the NULLS are forced to the end.

  • May 7, 2004 at 6:27 am

    #505648

    Antares686,

    Thanks - that works great!  Where can I find more information about these Case statements?  I need to beef up on my TSQL.

  • May 7, 2004 at 6:29 am

    #505650

    Best place of course is to start in BOL.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply