Results 1 to 5 of 5
  1. #1
    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. #2
    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. #3
    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. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Are there any data with quantity_new = 0 and quantity_used = 0?

  5. #5
    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
  •