Reputation: 699
I have a Product Database with products ordered by popularity (number_sold). The problem is that many of the popular products are out of stock. I want to sort by number_sold where in_stock is at least 1, then display the out-of-stock products afterwards (still sorted by number_sold).
Example Data:
Blue Car - stock 0 - sold 8
Red Car - stock 2 - sold 4
Yellow Car - stock 0 - sold 5
Tan Car - stock 8 - sold 1
Purple Car - stock 1 - sold 2
I want displayed in the following order:
1) Red
2) Purple
3) Tan
4) Blue
5) Yellow
Is this possible within one MySQL query?
Upvotes: 2
Views: 149
Reputation: 4167
You can use
select * from product order by (in_stock >0) desc, number_sold desc
Upvotes: 2
Reputation: 424983
Order by an expression based on the stock level first, then number sold:
select * from products
order by in_stock = 0, number_sold desc
This works because in mysql true is 1
and false is 0
, so for all the in-stock products, the expression in_stock = 0
is zero and will be sorted first. For out of stock products that expression is 1
sorting last.
Upvotes: 0