'My query won't work due to order by Clause

I am writing query for displaying data and added order by clause in that query. My query Is

SELECT * FROM `coupons` 
WHERE `status` = 'A' &&  `type` = '2'    
&&  `time` > CURDATE() && `start_date` <= CURDATE()
order by `stocks` asc

In above query it get sorted by 'stocks' but in some cases stocks is zero, that case I want show this row at the last.

mysql


Solution 1:[1]

order by CASE WHEN `stocks` = 0 THEN 1 ELSE 0 END asc , `stocks` asc

Solution 2:[2]

SELECT *, (if(`stocks` != 0,`stocks`,"")) as newstocks FROM `coupons` 
WHERE `status` = 'A' &&  `type` = '2'    
&&  `time` > CURDATE() && `start_date` <= CURDATE()
order by newstocks asc

Use above code.

Solution 3:[3]

By default it is asc so you don't have to mention it. Try the below one

SELECT * FROM coupons WHERE status = 'A' && type = '2' && time > CURDATE() && start_date <= CURDATE() order by case stocks != 0 then stocks end, stocks

It will first list the items with stocks is nozero and then list the rows with stocks=zero

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 valex
Solution 2
Solution 3 vinothM

Related Questions