Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Posts
    144

    Unanswered: Order By (Conside 0 as biggest)

    hi
    i want to 'order by asc' a result set by a numeric field, some of this numbers are 0 & i want these zeros come at the end of result set, something like this:
    Code:
    1
    2
    3
    4
    0
    0
    0
    0
    is it possible to get such a result with 'order by' ?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Code:
    order by
       case 
         when the_column = 0 then 999999999999
         else the_column
       end

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a bit more devious...
    Code:
    ORDER
        BY ISNULL(NULLIF(the_column,0))
         , the_column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Are NULLs always sorted at the top?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by shammat View Post
    Are NULLs always sorted at the top?
    in most database systems, yes

    in oracle it's the other way around

    now, why doesn't that surprise me?


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by r937 View Post
    in most database systems, yes
    Most databases allow me to control that with "NULLS FIRST" or "NULLS LAST" in the order by clause

    in oracle it's the other way around
    Only for ASC sort, for DESC sort it's the other way round. PostgreSQL, DB2 and Derby behave the same way.

    I had to look this up now
    According to the standard, it's left to the DBMS on how to behave is neither NULLS FIRST nor NULLS LAST is specified:
    Quote Originally Posted by SQL Standard
    If <null ordering> is not specified, then an implementation-defined <null ordering> is implicit.

Posting Permissions

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