SQLize | PHPize | SQLtest

CREATE TABLE product ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) ); INSERT INTO product (name) VALUES ('Prod1'),('Prod2'); CREATE TABLE prices ( id INT AUTO_INCREMENT PRIMARY KEY, product_id INT, price DECIMAL(9,2), timestamp datetime(6) ); INSERT INTO prices (product_id, price, timestamp) VALUES (1, 5.5, NOW()), (2, 6.2, NOW(6)), (1, 5.2, NOW(6)); SELECT * FROM prices; SELECT product.*, prices.price FROM product JOIN prices ON prices.product_id = product.id JOIN ( SELECT product_id, MAX(timestamp) AS last_price_date FROM prices GROUP BY product_id ) last_prices ON last_prices.product_id = prices.product_id AND last_prices.last_price_date = prices.timestamp ; SELECT * FROM ( SELECT product.*, prices.price , ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY timestamp DESC) rn FROM product JOIN prices ON prices.product_id = product.id ) prices WHERE rn = 1 ;