Thread: Order by with multiple conditions

1. Registered User
Join Date
Apr 2012
Posts
3

Unanswered: Order by with multiple conditions

I have rather hard case here. My product table looks like:

prod_id
name
quantity_new
quantity_used
price_new
price_used

I'm in need of ordering this table so that:
1) List is ordered by ascending price
2) LEAST(price_new, price_used) (whichever happens to be less), but with condition that saldo cannot be 0... which means more like LEAST(price_new AND quantity_new > 0, price_used AND quantity_used > 0)

Example 1:

Output (x marks which price caused ordering when using ORDER BY LEAST(price_new, price_used)):
Code:
```name     quantity_new    quantity_used    price_new    price_used

prod_3    1                    0             6.90          2.90 (x)
prod_2    0                    1             3.90 (x)      8.90
prod_1    1                    0             4.90 (x)     16.90
prod_6    0                    1             5.90 (x)     14.90
prod_4    1                    1            10.90 (x)     12.90```
What I want is (x marks which one I wanted to use for ordering, because of quantity):

Code:
```name     quantity_new    quantity_used    price_new    price_used

prod_1    1                   0             4.90 (x)     16.90
prod_3    1                   0             6.90 (x)      2.90
prod_2    0                   1             3.90          8.90 (x)
prod_4    1                   1            10.90 (x)     12.90
prod_6    0                   1             5.90         14.90 (x)```

Here we can see the problem. Prod 3 price_used is less than price_new, but I must ignore it because it's not in inventory, and rather use price_new (because new product is in inventory).

Another example (with bit different quantity bits for this example):

Code:
```SELECT *
FROM   prod
WHERE  1=1
AND (
(price_new  >= 4.90 AND price_new  <= 12.90 AND quantity_new > 0)   <-cond. 1
OR (price_used >= 4.90 AND price_used <= 12.90 AND quantity_used > 0)  <-cond. 2
)
ORDER BY LEAST(price_new, price_used) ASC```

Output is

Code:
```name     quantity_new  quantity_used  price_new    price_used

prod_3    1                   0             6.90       2.90    (filtered by cond. 1 and price_used is less)
prod_2    0                   1             3.90       8.90    (filtered by cond. 2 and price_new is less)
prod_1    1                   0             4.90      16.90    (filtered by cond. 1 and price_new is less)
prod_4    1                   1            10.90      12.90    (filtered by cond. 1 and price_new is less)```
.... When I want it to be .....

Code:
```prod_1    1                   0             4.90      16.90    (Because 4.90 is cheapest available)
prod_3    1                   0             6.90       2.90    (next cheapest with quantity)
prod_2    0                   1             3.90       8.90    (next cheapest with quantity)
prod_4    1                   1            10.90      12.90    (next cheapest with quantity)```

So basically I need to somehow tell that I want to order with combination of price and quantity, ignoring non-relevant combinations.

Could someone help with this?

2. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Two ideas.

(I don't know they would work on MySQL. They worked on DB2.)

Example 1:
Code:
```SELECT *
FROM  prod
ORDER BY
LEAST(
CASE quantity_new  WHEN 0 THEN 9999999.99 ELSE price_new  END
, CASE quantity_used WHEN 0 THEN 9999999.99 ELSE price_used END
)```
Example 2:
Code:
```SELECT *
FROM  prod
ORDER BY
CASE
WHEN quantity_new  = 1
AND quantity_used = 1 THEN
LEAST(price_new , price_used)
WHEN quantity_new  = 1 THEN
price_new
WHEN quantity_used = 1 THEN
price_used
END```

3. Registered User
Join Date
Apr 2012
Posts
3
The idea was what mattered, and your solution was right.

Also suggested on other forum:

Code:
```order by least(case when quantity_new = 0 then price_used else price_new end,
case when quantity_used = 0 then price_new else price_used end)```
Thank you for your time and pointing to right direction!

4. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Are there any data with quantity_new = 0 and quantity_used = 0?

5. Registered User
Join Date
Apr 2012
Posts
3
There could be, but in that case it's filtered by

(***something*** and quantity_new > 0) OR (***something*** and quantity_used > 0)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•