Blog » Useful SQL Snippet
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