#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    2
    Rep Power
    0

    sorting with NULLs at bottom


    I have a result set that I'm sorting by a nullable field. By default it puts the nulled rows at the top. I want them at the bottom without changing the order of the non-nulled rows.

    One way that I've thought to do this is some kind of combination of these queries:

    SELECT * from myTable
    WHERE NOT (myField IS NULL )
    ORDER BY myField

    and

    SELECT * FROM myTable WHERE myField IS NULL

    I suppose there's some kind of join construct for this, but I don't know it.

    Any help would be appreciated.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    Code:
    select * 
      from myTable 
    order 
        by case when myField is null then 1 else 0 end
         , myField
    rudy
    http://r937.com/

IMN logo majestic logo threadwatch logo seochat tools logo