Having a product_stock
table with following structure:
product_id
warehouse_id
stock
price
Which has bunch of records (same product can be in different warehouses):
1, 1, 0, 500
1, 2, 5, 505
1, 3, 7, 508
2, 1, 0, 400
2, 2, 0, 404
Now, for every product_id
, I want to select cheapest one in stock and if product is not in stock anymore, select price and warehouse as NULL - basically, the result should be:
1, 2, 5, 505
2, NULL, 0, NULL
Here's a sqlfiddle.
[Update]: Almost nailed it (now need to figure out how to select appropriate warehouse):
select product_id, min(price) from (
select product_id,
CASE WHEN stock = 0 then NULL else warehouse_id end,
CASE WHEN stock = 0 then NULL else price end from stock
) AS f group by product_id;
Returning (still need to figure out the warehouse_id
):
product_id min
1 505
2 (null)
[Update 2]: I was able to get the warehouse_id
, but this query kills the row without price:
SELECT stock.product_id, stock.warehouse_id, stock.price FROM (
SELECT product_id, min(price) as price FROM (
SELECT product_id,
CASE WHEN stock = 0 then NULL else warehouse_id end,
CASE WHEN stock = 0 then NULL else price end
FROM stock
) AS f GROUP by product_id
) AS ff JOIN
stock on stock.product_id=ff.product_id and stock.price = ff.price;
Result:
product_id warehouse_id price
1 2 505
\d tbl
in psql) where we can see exact data types, NULL clause, constraints, etc. – Erwin Brandstetter Mar 18 '15 at 12:15