mysqlsqljoinselect

Join newest price from price-table for all products


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?


Solution

  • 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;