1

I have two tables: first for products and second for prices.

enter image description here

I need to calculate and display the minimum price from all 5 prices for one product. I already have query to select five prices and dont know what to do next:

SELECT 
    wp_prices.price_one, 
    wp_prices.price_two, 
    wp_prices.price_three, 
    wp_prices.price_four, 
    wp_prices.price_five 
FROM wp_products
JOIN wp_prices ON wp_products.id = wp_prices.product_id
WHERE wp_prices.product_id = ' . $current_product_id . '
  • What DB are you using? And do you mean the minimum of the column (products repeated) or the minimum of the row values for a single entry of a product? – Dave Aug 5 '15 at 21:10
  • 1
    What's your DBMS? Does it support the LEAST function? – dnoeth Aug 5 '15 at 21:10
0

Using UNION in MySQL, this could work:

SELECT
    price.price_id,
    price.product_id,
    MIN(price.min_price) AS MinPrice
FROM (
        SELECT 
            p.price_id,
            p.product_id,
            p.price_one AS min_price
        FROM test.price AS p
        UNION ALL
        SELECT 
            p.price_id,
            p.product_id,
            p.price_two AS min_price
        FROM test.price AS p
        UNION ALL
        SELECT 
            p.price_id,
            p.product_id,
            p.price_three AS min_price
        FROM test.price AS p
        UNION ALL
        SELECT 
            p.price_id,
            p.product_id,
            p.price_four AS min_price
        FROM test.price AS p
        UNION ALL
        SELECT 
            p.price_id,
            p.product_id,
            p.price_five AS min_price
        FROM test.price AS p 
        ORDER BY price_id) 
AS price
-- WHERE price.price_id = whateverthatyouwanttosearch
GROUP BY price.price_id;

Test:

mysql> SELECT price.price_id, price.product_id, MIN(price.min_price) AS MinPrice FROM ( SELECT  p.price_id, p.product_id, p.price_one AS min_price FROM test.price AS p UNION ALL SELECT  p.price_id, p.product_id, p.price_two AS min_price FROM test.price AS p UNION ALL SELECT  p.price_id, p.product_id, p.price_three AS min_price FROM test.price AS p UNION ALL SELECT  p.price_id, p.product_id, p.price_four AS min_price FROM test.price AS p UNION ALL SELECT  p.price_id, p.product_id, p.price_five AS min_price FROM test.price AS p  ORDER BY price_id)  AS price   GROUP BY price.price_id;
+----------+------------+----------+
| price_id | product_id | MinPrice |
+----------+------------+----------+
|        1 |          3 |    33.00 |
|        2 |          2 |    23.00 |
|        3 |          4 |    23.00 |
|        4 |          5 |    22.00 |
|        5 |          6 |    12.00 |
+----------+------------+----------+
5 rows in set (0.00 sec)
2

Of course your data is denormalized, otherwise it would be a simple MIN(price) :-)

If you got luck your DBMS supports a LEAST function:

LEAST(price_one,price_two,price_three,price_four,price_five),

A huge CASE is supported in every DBMS:

CASE
  WHEN price_one <= price_two
   AND price_one <= price_three
   AND price_one <= price_four
   AND price_one <= price_five
  THEN price_one

  WHEN price_two <= price_three
   AND price_two <= price_four
   AND price_two <= price_five
  THEN price_two

  WHEN price_three <= price_four
   AND price_three <= price_five
  THEN price_three

  WHEN price_four <= price_five
  THEN price_four

  ELSE price_five
END

Hopefully there are no NULLs...

0

It is easy to do if you unpivot the data with SQL Server:

Select p.price_id, p.product_id, min(price)
From prices as p
Unpivot(
    price for p in (price_one, price_two, price_three, price_four, price_five)
) as p
group by p.price_id, p.product_id
order by p.price_id

Whole query with test data:

-- your data
; With prices(price_id, product_id, price_one, price_two, price_three, price_four, price_five) as (
    Select price_id, product_id, price_one, price_two, price_three, price_four, price_five
    From (values(1, 3, 66, 88, 67, 89, 33)
        , (2, 2, 23, 56, 99, 87, 45)
        , (3, 4, 44, 66, 23, 88, 32)
        , (4, 5, 43, 22, 56, 77, 65)
        , (5, 6, 45, 33, 87, 122, 12)
    ) as p(price_id, product_id, price_one, price_two, price_three, price_four, price_five)
)
-- the query you want
Select p.price_id, p.product_id, min(price)
From prices as p
Unpivot(
    price for p in (price_one, price_two, price_three, price_four, price_five)
) as p
group by p.price_id, p.product_id
order by p.price_id

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.