Getting Max Value From Joined Table With Certain Logic

Question

This is my table relation :

tbl_product
-----------
product_id

tbl_product_price
-----------------
price_id
price_product_id (FK)
price_normal 
price_discount
price_disc_valid_from_date 
price_disc_valid_to_date

I'd like to query tbl_product, ordered by its max price DESC, which must be validated first. If the the discount date is still valid (current date between price_disc_valid_from_date AND price_disc_valid_to_date), then get the price_discount. If not valid, then get price_normal. After that I need to get max price ( either from the price_discount or price_normal), then order by that max price.

Most of the questions like this are just how to select the max column, no validation needed first on the joined table.

My question is , what is the postgres sql statement for that query ? Thanks

[EDIT]

I stuck in selecting max price from table tbl_product_price but no idea how to join with tbl_product :

SELECT 
    pr.price_id, pr.product_price_id, 
    CASE WHEN current_date BETWEEN pr.price_disc_valid_from_date AND pr.price_disc_valid_to_date 
        THEN pr.price_discount 
        ELSE pr.price_normal END AS price 
    FROM tbl_product_price pr 
    WHERE pr.price_product_id = 316 
    GROUP BY pr.price_id, pr.price_product_id 
    ORDER BY price DESC 
    LIMIT 1;

I believe you should look into the `case` statement: http://www.postgresql.org/docs/9.4/interactive/functions-conditional.html#FUNCTIONS-CASE — Politank-Z, May 28 '15 at 13:35
You should include your code in your question, not in comments. Does Arion's answer give you what you need? — Politank-Z, May 28 '15 at 13:48
a product can have more than one price and more than one discount, and you want to order by the maximum price, using the logic you decribed, but regardless the product is discounted or not? can you show some sample data and a desidered result? — fthiella, May 28 '15 at 14:01

score 0 · Accepted Answer · answered May 28 '15 at 13:45

Can you do something like this:

SELECT
    tbl_tbl_product.price_product_id,
    tblMax.MaxPrice
FROM
    tbl_tbl_product
    JOIN
    (
        SELECT
            tbl_product_price.price_product_id, 
            MAX(
                CASE 
                    WHEN now() BETWEEN
                               tbl_product_price.price_disc_valid_from_date 
                               AND tbl_product_price.price_disc_valid_to_date
                    THEN tbl_product_price.price_discount
                    ELSE tbl_product_price.price_normal
                END
            ) AS MaxPrice  
        FROM
            tbl_product_price
        GROUP BY
            tbl_product_price.price_product_id
    ) as tblMax
    ON tblMax.price_product_id=tbl_tbl_product.product_id
ORDER BY
    tblMax.MaxPrice DESC

Subselect inside join is unnecessary and it's introducing complexity. You can rewrite query to avoid it. — Radek Postołowicz, May 28 '15 at 14:57
@RadekPostołowicz : yes I know. But not if the op has more columns in the product table. Which is not part of the question — Arion, May 28 '15 at 19:52

fthiella · Answer 2 · 2015-05-28T15:03:39.523

If I understand your logic correctly, this query should return products ordered by the maximum price for the product:

SELECT
  tp.product_id
FROM
  tbl_product tp INNER JOIN tbl_product_price tpp
  ON tb.product_id = tpp.price_product_id
GROUP BY
  tp.product_id
ORDER BY
  MAX(CASE WHEN current_date BETWEEN tpp.price_disc_valid_from_date
                             AND tpp.price_disc_valid_to_date THEN
      tpp.price_discount
    ELSE
      tpp.price_normal END) DESC

Getting Max Value From Joined Table With Certain Logic

2 Answers2