CODEWITHSUNDEEP
mysql
Chris
Chris

Reputation: 27394

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

Upvotes: 3

Views: 96

Answers (3)

Matt C-E
Matt C-E

Reputation: 11

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.

Upvotes: 1

Eugen Rieck
Eugen Rieck

Reputation: 65304

SELECT product_price FROM tablename ORDER BY IF(product_price=0,4294967295,product_price) ASC

Upvotes: 2

Ken White
Ken White

Reputation: 125728

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.

Upvotes: 3

Related Questions