SQLize | PHPize | SQLtest

CREATE TABLE IF NOT EXISTS `prices` ( id int primary key, size int, price int, date_time date ); INSERT INTO `prices` VALUES (0, 30, 800, '2021-10-01'), (1, 30, 900, '2021-10-02'), (2, 32, 700, '2021-09-11'), (3, 30, 800, '2021-09-21'), (4, 32, 800, '2021-09-01'), (5, 32, 0, '2021-10-03'); SELECT prices.* FROM `prices` JOIN ( SELECT size, MAX(date_time) date_time FROM prices GROUP BY size ) last_price USING (size, date_time) WHERE -- model_id = '269' AND partner_id = '0' AND size <= '32' AND date_time <= '2021-10-19' ORDER BY size DESC, date_time DESC; SELECT * FROM ( SELECT prices.*, ROW_NUMBER() OVER (PARTITION BY size ORDER BY date_time DESC) last_price FROM `prices` WHERE -- model_id = '269' AND partner_id = '0' AND size <= '32' AND date_time <= '2021-10-19' ) data WHERE last_price = 1 ORDER BY size DESC;