SQL Server 2005 Order BY with an expression

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.

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.

you can use

ORDER BY CASE WHEN condition= 1 THEN 1 ELSE 2 END

you can order by the ordinal position of the column, if you want to SEE the data that you're sorting by... for example, if you want to order by the 1st column, just say 'ORDER BY 1'. Here is an example where I add an expression in the select clause.. and then I order by it in the order by clause

SELECT colX0, 
(case WHEN colX1 IS NULL THEN 1 ELSE 0 END )
 FROM tbp_name 
ORDER BY 2 

You'd need to put it in your select first

SELECT 
  colX0, 
  CASE WHEN colX1 IS NOT NULL THEN 0 ELSE 1 END AS [COMPUTED1]
FROM tbp_name 
ORDER BY COMPUTED1

It's something like that anyway off the top of my head. http://www.tizag.com/sqlTutorial/sqlcase.php