SQL Server 2005 Order BY with an expression

Question

Is there a possibility to order the result by an ORDER clause that contains an expression, something like

SELECT colX0 FROM tbp_name ORDER BY (colX1 IS NOT NULL)

or also a more complex expression ?

UPDATE:

In the meanwhile I have found a possibility to solve the above problem:

ORDER BY (case WHEN colX1 IS NULL THEN 1 ELSE 0 END ) ASC

however the question remains, if there is a possibility to order direct by an expression.

Answer 1

No, SQL Server does not support direct conversion of an expression to true/false.

IMHO, one reason is the 3-valued logic. This has 3 outcomes, not 2, of either column is NULL. The NULL is first in SQL generally, always Server but can be specified last in other RDBMS.

 ORDER BY (colX1 = colX2)

Using CASE mitigates this and removes ambiguity

 ORDER BY
   CASE
       WHEN colX1 = colX2 THEN 1
       WHEN colX1 <> colX2 THEN 2
       ELSE 3 NULL case
   END

You have to use CASE as per your update, as well ensuring datatypes match (or at least implicitly convertable) in WHEN clauses.