Order by, put 0's at the end whilst maintaining the ascending search order
I have a query that uses ORDER BY ASC and as such 0's come up first. I would like them to come up last whilst still maintaining the ascending search order. How can I achieve this?
An example of this is:
SELECT product_price ORDER BY product_price ASC
So instead of
0 1 2 3
I would want
1 2 3 0
I don't have MySQL to test against, but this works in SQL Server and Advantage Database Server:
SELECT product_price ORDER BY CASE product_price WHEN 0 then 99999999 ELSE product_price END
Replace the 99999999 series with the maximum value of the product_price column type.
SELECT product_price FROM tablename ORDER BY IF(product_price=0,4294967295,product_price) ASC
For the best performance, use a union.
( SELECT * FROM products WHERE product_price > 0 ORDER BY product_price ASC ) UNION ( SELECT * FROM products WHERE product_price = 0 )
The alternative, uses filesort which can be really slow when you have large tables.
EXPLAIN SELECT * FROM products ORDER BY IF(product_price = 0, 1, 0) ASC ,product_price ASC
yeilds Using filesort. This means a temporary table is being written on disk. Very slow when you're dealing with large queries.