Update: Benjamin Wilger came up with an even better solution.
Maybe this will come in handy for someone else. I have a table of products in a database and I want to select from it with the products in such an order that products that are out of stock are last. However, I don't want the in stock products to be ordered by how much stock is available (i.e. I can't just order by stock descending). I don't want to add another column with a bit to indicate whether or not a product is in stock, because ... well, I just don't want to.
You can't do this, which is what I wanted:
SELECT CAST((Stock > 0) AS BIT) AS InStock FROM table
But you can do this, which is the same (as long as you have under ten million of everything in stock):
SELECT CAST(CEILING(CAST(Stock AS FLOAT) / 10000000) AS BIT) AS InStock FROM table
AddedBytes.com is the online playground of 
SELECT ... FROM products ORDER BY (InStock > 0) DESC, ProductName ASC (or whatever)
Try it. It will sort all products to the end, if they are not in stock, but won't actually scramble the entire table sorting.
Greets,
Benjamin