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?

Answer 1

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;