SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE move_history ( id integer, key varchar(20), status varchar(255), created timestamp ); INSERT INTO move_history (id, key, status, created) VALUES (1, 'VK-333', 'COMPLETED', '2022-01-01'), (2, 'VK-333', 'COMPLETED', '2022-01-02'), (3, 'VK-111', 'CANCELED', '2022-01-01'), (4, 'PA-222', 'COMPLETED', '2022-01-01'), (5, 'PA-222', 'CANCELED', '2022-01-02'), (6, 'PA-222', 'COMPLETED', '2022-01-03'), (7, 'PA-222', 'COMPLETED', '2022-01-04'), (8, 'VK-444', 'CANCELED', '2022-01-01'); WITH d AS ( SELECT *, row_number() over (partition by key order by created desc) rn FROM move_history ) SELECT * FROM d WHERE status = 'COMPLETED' AND rn = 1 ORDER BY id;