CODEWITHSUNDEEP
mysql
Danny
Danny

Reputation: 47

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.

Upvotes: 0

Views: 66

Answers (3)

vinothM
vinothM

Reputation: 195

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

Upvotes: 0

valex
valex

Reputation: 24144

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

Upvotes: 2

user2334807
user2334807

Reputation: 

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.

Upvotes: 0

Related Questions