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
17 Comments
Why not just do that?
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
#1, Benjamin Wilger, Germany, 21 May 2007. Reply to this.
It is a very practical and insightful solutuion, sure! Thanks for sharing it.
But, for some reason, I can't cope with the idea that you have to be under a certain value. Even if you use an absurdly high number, doesn't seem right to me (must be some kind of computer science college trauma I still carry with me).
I guess I'd do two searches one for inStock and one for outOfStock and concatenate.
I know my solution is far from brilliant and insightful, but seems more correct.
#2, Guilherme Zühlke O'Connor, Brazil, 21 May 2007. Reply to this.
Benjamin's solution seems great. I didn't know you could use logical expressions to perform SQL queries, but it really makes sense...
#3, Guilherme Zühlke O'Connor, Brazil, 21 May 2007. Reply to this.
Benjamin: Even better :). I didn't actually know you could use expressions in the order by.
Guilherme: I know what you mean - I had the same nagging feeling of dread when I put a real number in there. Seemed somehow wrong. Still, as you say, Ban's solution is even better!
#4, Dave Child, Unknown, 21 May 2007. Reply to this.
Every expression is possible because they're evaluated to values. The value of (x > y) is 1 or 0, because it's boolean. Why shouldn't it possible to sort by those values? :-)
As another example you can generate a randomized resultset with this query:
SELECT * FROM tbl ORDER BY RAND()
RAND() is just a function that returns a random value between 0 and 1, which can be used for sorting.
#5, Benjamin Wilger, Germany, 21 May 2007. Reply to this.
I knew about RAND() for ordering in MySQL and NEWID() in SQL Server - for some reason didn't think to try expressions in the ordering. Always good to find a better way to do something though :)
#6, Dave Child, Unknown, 21 May 2007. Reply to this.
Ben, yours is a very nice and elegant solution.
I understood it when I saw it, that's why I said it makes sense, but I wouldn't have think of it myself, I guess (Perhaps now I would...)
I would be searching for a number, not a "boolean" for that field.
#7, Guilherme Zühlke O'Connor, Brazil, 21 May 2007. Reply to this.
A plain and elegant solution indeed.
Both thumbs up !!
#8, Loke Krongaard Hansen, Denmark, 24 May 2007. Reply to this.
Thanks Benjamin, That's indeed a great solution. :)
#9, Bandolino, Australia, 26 May 2007. Reply to this.
Benjamin rocks!
#10, Ad, United States, 29 May 2007. Reply to this.
Thanks for sharing!
#11, The King, United States, 7 June 2007. Reply to this.
Can't believe - it works. Thanks lot!
#12, Tino, Germany, 7 June 2007. Reply to this.
I had some problems with Benjamin's solution (kept getting an error saying "Incorrect syntax near '>'."). I changed it to this (virtually the same) and it worked fine:
ORDER BY (cast(ProductStock as bit)) DESC
#13, Dave Child, United Kingdom, 15 June 2007. Reply to this.
hm sounds strange. Maybe you use a very old version of MySQL (3.23)? MySQL should allow expressions inside of ORDER BY clauses in Versions above 4.
Greetings,
Ben
#14, Benjamin Wilger, Germany, 8 July 2007. Reply to this.
Hi Benjamin,
I am using a very odd version of MySQL indeed ... SQL Server :)
Probably should have made that clearer before. Sorry.
#15, Dave Child, United Kingdom, 13 July 2007. Reply to this.
Benjamin's solution is amazing.
Nice way to use logical expressions for sql queries, i was looking for that
#16, seo la, Unknown, 9 September 2007. Reply to this.
if you want to see the column - use:
Select CASE WHEN stock > 0 THEN 1 ELSE 0 END as InStock FROM Table
ORDER BY InStock
#17, JD, United States, 18 April 2009. Reply to this.