Join newest price from price-table for all products

Question

I've a product and a price table (with product id, price and timestamp columns). Now I need to join to a simple select of products the newest price.

The simple join will select the first saved price in the table but not the newest.

    SELECT p.*, pp.price
    FROM products p
    INNER JOIN product_prices pp ON (pp.product_id = p.id)

So I've tried to join with select the newest timestamp, but this will only return the newest timestamp for all records and not per product_price:

    SELECT p.*, pp.*
    FROM products p
    LEFT JOIN
        (SELECT product_id, ranking, MAX(timestamp) as timestamp
        FROM product_prices) pp 
        ON pp.product_id = p.id

Is there an smart/right way to select all products with the newest price per product?

`The simple join will select the first saved price in the table but not the newest.` Er, no. It will select all of them. — Strawberry, Jun 15 '19 at 15:32
@Shadow . . . There is, no doubt, a duplicate somewhere. But the "duplicate" provided only referenced one table, which is not the same as this question. — Gordon Linoff, Jun 15 '19 at 15:44
@GordonLinoff the question was how to find the max per group, not on how to join two tables. The OP clearly knows how to do th latter. You just keep answering the same questions to inflate your score, rather than doing the right thing and close the questions. — Shadow, Jun 15 '19 at 15:47
@GordonLinoff https://stackoverflow.com/questions/20989828/select-latest-record-from-right-table-when-using-join-in-mysql this question is about two tables. Do the honourable thing and close the question as a duplicate yourself. — Shadow, Jun 15 '19 at 15:57

Gordon Linoff · Answer 1 · 2019-06-15T15:32:02.493

You are close, but you need a GROUP BY in the subquery:

SELECT p.*, pp.*
FROM products p LEFT JOIN
     (SELECT product_id, MAX(timestamp) as timestamp
      FROM product_prices
      GROUP BY product_id
     ) pp 
     ON pp.product_id = p.id;

It is not clear what the ranking column is about. It is only used in your query and not mentioned elsewhere in the question.

If you want the entire product_price row, you can use an additional JOIN:

SELECT p.*, pp.*
FROM products p JOIN
     product_prices pp
     ON pp.product_id = p.id LEFT JOIN
     (SELECT product_id, MAX(timestamp) as timestamp
      FROM product_prices
      GROUP BY product_id
     ) ppm
     ON ppm.product_id = p.id AND ppm.timestamp = pp.timestamp;

score 0 · Accepted Answer · answered Jun 15 '19 at 15:30

I think you need two joins here. The first join is directly to the product_prices table, to bring in the columns you want from that table. The second join is to a subquery which aggregates product_prices by product, to find the most recent entry for each product. This second join then restricts the full results coming from the first join.

SELECT p.*, pp1.*
FROM products p
LEFT JOIN product_prices pp1
    ON p.id = pp1.product_id
INNER JOIN
(
    SELECT product_id, MAX(timestamp) AS max_ts
    FROM product_prices
    GROUP BY product_id
) pp2
    ON pp1.product_id = pp2.product_id AND
       pp1.timestamp = pp2.max_ts;

If you are using MySQL 8+, then we can take advantage of window functions here:

WITH cte AS (
    SELECT p.*, pp1.*,
        ROW_NUMBER() OVER (PARTITION BY p.id ORDER BY pp.timestamp DESC) rn
    FROM products p
    LEFT JOIN product_prices pp
        ON p.id = pp.product_id
)

SELECT *
FROM cte
WHERE rn = 1;

Join newest price from price-table for all products

2 Answers2