CODEWITHSUNDEEP
mysqlsql-order-by
Jan Richter
Jan Richter

Reputation: 2086

MySQL ORDER BY two fields condition

I have a problem with creating select query ordered by following logic:

SELECT * FROM Products WHERE 1 ORDER BY Stock > 0, Price DESC

Table sample:

+---------+-------+-------+
| Product | Price | Stock |
+---------+-------+-------+
| Car     |  3500 |    30 |
| Boat    |  7500 |     6 |
| Bike    |   150 |   220 |
| Plane   | 55000 |     0 |
+---------+-------+-------+

The desired result is, that the table will be ordered by price if the stock value is greater than 0.

So the result should be:

+---------+-------+-------+
| Product | Price | Stock |
+---------+-------+-------+
| Boat    |  7500 |     6 |
| Car     |  3500 |    30 |
| Bike    |   150 |   220 |
| Plane   | 55000 |     0 |
+---------+-------+-------+

Any ideas?

Upvotes: 0

Views: 1306

Answers (1)

juergen d
juergen d

Reputation: 204766

The result of a comparison in MySQL is 0 or 1. So Stock > 0 is 1 if true. And 1 and greather than 0. So either use

ORDER BY Stock = 0 ASC, Price DESC

or 

ORDER BY Stock > 0 DESC, Price DESC

or

ORDER BY case when Stock > 0
              then 1
              else 2
         end, 
         Price DESC

Upvotes: 2

Related Questions