Sort by price, quantity low-> high

Question

I read so many questions/answers on stack overflow but no one give the answer which I want to require.

Here is the my result query. I have required result of sort by price low-> high but it gives me a result that starts with records that have the price 0. This is correct, but I don't want the 0 price records first. Result should start with prices greater than 0 and 0 price records should be displayed last.
Please help me to solve my this query.

For example, the result should start start with price 125.0, 125.0 ... 0, 0

Answer 1

You can try this:

SELECT 
    product_id, quantity, price, 
    CASE price WHEN 0 
        THEN 1
        ELSE 0
    END
    as is_price_zero
FROM
    product
ORDER BY
    is_price_zero ASC,
    price ASC

Answer 2

Okay I think you need all the Price accending products first and than the products with 0 price

So you can run a query to get your data without 0 price values

SELECT * FROM 
(SELECT * FROM `product` WHERE price > 0
ORDER BY  price ASC LIMIT 0 , 30) as first_query

Than run a query to get data of 0 price values

SELECT * FROM `product` WHERE price = 0

and than union so final query will be

SELECT * FROM 
(SELECT * FROM `product` WHERE price > 0
ORDER BY  price ASC LIMIT 0 , 30) as first_query UNION SELECT * FROM `product` WHERE price = 0

Just change your field name as per your requirements.