Question 89705 in PostgreSQL Question posted in
The official documentation can be found here.

SQL Select MAX value only when condition not match – Postgresql

I want to select max prices only when certain condition on the table are met. The table consist of this column

| id | product_item_code | distribution_channel | sap_no_shipto | valuation_type | price |
|----|-------------------|----------------------|---------------|----------------|-------|
| 1  | A                 | A                    | A             | NULL           | 200   |
| 2  | A                 | A                    | A             | B              | 2000  |
| 3  | A                 | A                    | A             | C              | 3000  |

I want to apply this logic

  1. If the valuation_type are NULL use the price from that row
  2. If the valuation_type are not null, use the highest price

Table have unique constrain combination

product_item_code | distribution_channel | sap_no_shipto | valuation_type

For now, I code it like this, but it doesn’t satisfy the first condition.

Below are the subquery. Is there any way to apply the logic? Because this query only select the MAX price if there are null on valuation type, it will ignore it.

            SELECT MAX(psp.price)
            FROM product_special_prices psp
            WHERE
              psp.product_item_code = p.item_code AND
              psp.distribution_channel = st.distribution_channel AND
              psp.sap_no_shipto = st.sap_no_shipto
            GROUP BY (
              psp.product_item_code,
              psp.distribution_channel,
              psp.sap_no_shipto
            )
Tags:

3

Answers


  1. 0

    You can order by price and take the highest:

    SELECT DISTINCT ON (product_item_code, distribution_channel, sap_no_shipto)
       price
FROM product_special_prices
ORDER BY product_item_code, distribution_channel, sap_no_shipto,
         valuation_type IS NOT NULL, price DESC;

    This will sort the rows with valuation_type of value NULL first, because FALSE < TRUE. DISTINCT ON will return the first row for each group.

    Login or Signup to reply.
  2. 0

    Another option, you could use the coalesce and filtered max function as the following:

    select product_item_code, distribution_channel, sap_no_shipto,
       coalesce(max(price) filter (where valuation_type is null),
                max(price) filter (where valuation_type is not null)) mx
from product_special_prices
group by product_item_code, distribution_channel, sap_no_shipto

    Demo

    Login or Signup to reply.
  3. 0

    Try with this

    SELECT *
    ,NewColumn = CASE WHEN valuation_type IS NOT NULL THEN tb.price ELSE tb2.PriceMax END
    FROM table tb

    OUTER APPLY (
    SELECT PriceMax = MAX(price) FROM table
    ) tb2

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search