Aggregate query with min

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

This can be much simpler, yet, with DISTINCT ON:

SELECT DISTINCT ON (product_id)
       product_id
     , CASE WHEN stock = 0 THEN NULL ELSE warehouse_id END AS warehouse_id
     , stock
     , CASE WHEN stock = 0 THEN NULL ELSE price END AS price
FROM   product_stock
ORDER  BY product_id, (stock = 0), price;

Assuming stock to be NOT NULL.

SQL Fiddle.

About DISTINCT ON:

• Select first row in each GROUP BY group?

Postgres has a proper boolean type and one can ORDER BY any boolean expression. FALSE sorts before TRUE sorts before NULL. So rows with (stock = 0) sort behind rows with any other value for stock - except NULL, which would sort last.

StackExchange Database Administrators Q#95551, answer score: 3