CODEWITHSUNDEEP
phpmysqlsorting
Lovntola
Lovntola

Reputation: 1409

How to sort with 0 on the end in result

I have a list of products with prices. Some products are prices only aviable on request. This products are labeled with a price of 0 in the database.
If I now make a request like:

$sql = "SELECT * FROM products WHERE ORDER BY Price ASC";

The Results are of course starts with a price of 0.
But I want all the 0 prices at the end of the results.
Is this possible with a SQL request or what is the best solution to re-sort in php?
Thx in advanced
Bernd

Upvotes: 0

Views: 72

Answers (3)

The Coder
The Coder

Reputation: 87

You can mysql FIELD function to retrieve the data in the order you want

   SELECT * FROM products WHERE ORDER BY FIELD(Price,0) ASC

Upvotes: -1

Dazz Knowles
Dazz Knowles

Reputation: 534

Yes you can by using bitwise negation to make 0 the maximum value for a bigint in the sort.

SELECT *
FROM products
ORDER BY
    CASE WHEN Price = 0 THEN ~0 ELSE Price END ASC

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522151

You may sort using a CASE expression which places zero prices last:

SELECT *
FROM products
WHERE ...
ORDER BY
    CASE WHEN Price > 0 THEN 0 ELSE 1 END,
    Price;

In MySQL, the ORDER BY clause can be simplified:

SELECT *
FROM products
WHERE ...
ORDER BY
    Price = 0,    -- false (0) for positive prices, true (1) for zero prices
    Price;

Upvotes: 4

Related Questions