Useful SQL Snippet

This post has been archived. It's probably out of date or shameful in some other way.

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

21 May 2007