Order By (Conside 0 as biggest)

bono56 · #1 (**permalink**) 01-21-12, 19:01

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:

is it possible to get such a result with 'order by' ?

shammat · #2 (**permalink**) 01-21-12, 19:10

Code:

order by
   case 
     when the_column = 0 then 999999999999
     else the_column
   end

r937 · #3 (**permalink**) 01-21-12, 19:16

a bit more devious...

Code:

ORDER
    BY ISNULL(NULLIF(the_column,0))
     , the_column

shammat · #4 (**permalink**) 01-22-12, 03:25

Are NULLs always sorted at the top?

r937 · #5 (**permalink**) 01-22-12, 03:35

Quote:

Originally Posted by shammat

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?

shammat · #6 (**permalink**) 01-22-12, 04:41

Quote:

Originally Posted by r937

in most database systems, yes

Most databases allow me to control that with "NULLS FIRST" or "NULLS LAST" in the order by clause

Quote:

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech