CODEWITHSUNDEEP
sqlmysqlsql-order-by
Luciano
Luciano

Reputation: 1455

Using 2 fields in ORDER BY clause

I have a page that show "special offers", and i need to order the results by discount value. Besides i want that products with quantity=0 are shown at the end of the list (regardless of the discount value).

So, there is any way to do that using only SQL? I mean... if i set "ORDER BY discount, quantity DESC" the list show products ordered by discount, and each groups of discout is ordered by the quantity value... this isn't what i want.

Thanks in advance...

Upvotes: 0

Views: 120

Answers (2)

aularon
aularon

Reputation: 11110

SELECT * FROM `products` ORDER BY discount WHERE quantity > 0
UNION SELECT * FROM `products` WHERE quantity <= 0;

Like this?

Upvotes: 0

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25370

ORDER BY CASE Quantity WHEN 0 THEN 99999999 ELSE Discount END, Quantity DESC

Upvotes: 6

Related Questions