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

Answers


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.


Need Your Help

Nuget install-package and network shares

c# msbuild nuget

We have a private nuget repository for sharing internal components. For now, we are just using a network share.

Display ASP.NET Control on Event

asp.net javascript vb.net datepicker

I'm looking to create a custom date picker with code examples from several sources.