2

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
  • I tried various if-else approaches, though I guess I actually need to do a self-referencing join. – plaes Mar 18 '15 at 10:05
  • Your approach is ok. What are you going to do about duplicate min-prices? This will get you a bit further: select * 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 ) AS f from product_stock group by product_id) min_prices join product_stock stock on stock.price=min_prices.price and stock.product_id=min_prices.product_id ; – Falcon Mar 18 '15 at 10:36
  • 1
    Which version of Postgres do you use? – ypercubeᵀᴹ Mar 18 '15 at 11:20
  • @ypercube: Postgres 9.3+ – plaes Mar 18 '15 at 11:24
  • 1
    9.3+? Is that 9.3 or 9.4? And table definition: it is always much better to post the actual table definition (what you get with \d tbl in psql) where we can see exact data types, NULL clause, constraints, etc. – Erwin Brandstetter Mar 18 '15 at 12:15
2

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:

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.

| improve this answer | |
  • 1
    @plaes: No, not at all. (stock = 0) only sorts numbers before 0 and NULL. I.e. it leaves numbers unsorted otherwise, so that sorting by price in the example above actually works. – Erwin Brandstetter Mar 18 '15 at 16:21
  • Wonderful - PostgreSQL docs should really have an example of such ORDER BY expression. – plaes Mar 18 '15 at 17:42
1

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.

| improve this answer | |
  • Wow! :) (after fixing following typo: s/product_stock/stock) – plaes Mar 18 '15 at 11:37
  • Your question starts with: "Having a product_stock table ..." – ypercubeᵀᴹ Mar 18 '15 at 11:40
  • Ah.. indeed, in my sqlfiddle that I created later, I had simply stock. – plaes Mar 18 '15 at 11:41
  • And my price in the external query should have a CASE as well, to show NULL and not the price from the warehouse with 0 stock. – ypercubeᵀᴹ Mar 18 '15 at 11:41
0

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)
| improve this answer | |

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.