Aggregate query with min

Question

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

Answer 1

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.

Answer 2

Two similar versions. One:

-- query 1 --
WITH a AS
  ( SELECT 
        product_id, warehouse_id, stock, price,
        MIN(CASE WHEN stock > 0 THEN price END) 
            OVER (PARTITION BY product_id)
          AS min_price,
        MIN(CASE WHEN stock = 0 THEN price END) 
            OVER (PARTITION BY product_id)
          AS min_non_stock_price
    FROM stock
  )  
SELECT product_id, 
       CASE WHEN stock > 0 THEN warehouse_id END AS warehouse_id, 
       stock, 
       CASE WHEN stock > 0 THEN price END AS price
FROM a 
WHERE stock > 0         AND price = min_price
   OR min_price IS NULL AND price = min_non_stock_price ;  

and two:

-- query 2 --
WITH a AS
  ( SELECT 
        product_id, warehouse_id, stock, price,
        MIN(CASE WHEN stock > 0 THEN price END) 
            OVER (PARTITION BY product_id)
          AS min_price
    FROM stock
  )  
SELECT product_id, warehouse_id, stock, price
FROM a 
WHERE price = min_price 

UNION ALL

SELECT product_id, NULL, 0, NULL
FROM a 
WHERE min_price IS NULL
GROUP BY product_id ; 

Tested at SQLfiddle.

Answer 3

I eventually tried with CTE-s:

WITH min_prices AS (
  SELECT product_id, min(price) price FROM (
    SELECT product_id,
      CASE WHEN stock = 0 then NULL else price end
    FROM stock) as _ GROUP BY product_id
), existing_stock AS (
  SELECT product_id, warehouse_id, price FROM stock WHERE stock > 0
)
SELECT min_prices.product_id,
       existing_stock.warehouse_id,
       min_prices.price FROM min_prices
LEFT JOIN existing_stock ON
    min_prices.product_id = existing_stock.product_id AND
    min_prices.price = existing_stock.price;

Resulting in win :)

product_id  warehouse_id    price
1   2   505
2   (null)  (null)

Execution plan doesn't look that impressive, though:

Hash Join  (cost=49.97..114.58 rows=3 width=12)
  Hash Cond: ((stock.product_id = min_prices.product_id) AND (stock.price = min_prices.price))
CTE min_prices
    ->  HashAggregate  (cost=40.97..42.97 rows=200 width=12)
    ->  Seq Scan on stock stock_1  (cost=0.00..27.70 rows=1770 width=12)
    ->  Seq Scan on stock  (cost=0.00..32.12 rows=590 width=12)
Filter: (stock > 0)
    ->  Hash  (cost=4.00..4.00 rows=200 width=8)
    ->  CTE Scan on min_prices  (cost=0.00..4.00 rows=200 width=8)