clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3799123 fiddles created (41768 in the last week).

CREATE TABLE devices(id INTEGER PRIMARY KEY, name VARCHAR UNIQUE);
 hidden batch(es)


CREATE TABLE results(id INTEGER PRIMARY KEY, test_name VARCHAR, results JSON, state VARCHAR);
 hidden batch(es)


CREATE TABLE results_for_device(results_id INTEGER, device_id INTEGER, PRIMARY KEY(results_id, device_id));
 hidden batch(es)


INSERT INTO devices(id, name) VALUES (1, 'Dev1'), (2, 'Dev2');
2 rows affected
 hidden batch(es)


INSERT INTO results(id, test_name, results, state) VALUES -- Results thread for 'test1' and 'Dev1' (1, 'test1', '{}', 'CURRENT_BLUEPRINT'), (2, 'test1', '{}', 'MATCHING_BLUEPRINT'), (3, 'test1', '{}', 'MATCHING_BLUEPRINT'), (4, 'test1', '{}', 'NOT_MATCHING_BLUEPRINT'), -- Results thread for 'test1' and 'Dev2' (5, 'test1', '{}', 'OLD_BLUEPRINT'), (6, 'test1', '{}', 'NOT_MATCHING_BLUEPRINT'), (7, 'test1', '{}', 'CURRENT_BLUEPRINT'), (8, 'test1', '{}', 'MATCHING_BLUEPRINT'), (9, 'test1', '{}', 'MATCHING_BLUEPRINT'), -- Results thread for 'test2' and 'Dev1' (10, 'test2', '{}', 'OLD_BLUEPRINT'), (11, 'test2', '{}', 'NOT_MATCHING_BLUEPRINT'), (12, 'test2', '{}', 'CURRENT_BLUEPRINT'), (13, 'test2', '{}', 'MATCHING_BLUEPRINT'), (14, 'test2', '{}', 'MATCHING_BLUEPRINT'), -- Results thread for 'test2' and 'Dev2' (15, 'test2', '{}', 'OLD_BLUEPRINT'), (16, 'test2', '{}', 'CURRENT_BLUEPRINT'), (17, 'test2', '{}', 'MATCHING_BLUEPRINT'), (18, 'test2', '{}', 'MATCHING_BLUEPRINT'), (19, 'test2', '{}', 'NOT_MATCHING_BLUEPRINT');
19 rows affected
 hidden batch(es)


INSERT INTO results_for_device(results_id, device_id) VALUES (1, 1), (2, 1), (3, 1), (4, 1), (5, 2), (6, 2), (7, 2), (8, 2), (9, 2), (10, 1), (11, 1), (12, 1), (13, 1), (14, 1), (15, 2), (16, 2), (17, 2), (18, 2), (19, 2);
19 rows affected
 hidden batch(es)


WITH result_threads AS ( SELECT rfd.*, r.test_name FROM results_for_device AS rfd INNER JOIN results AS r ON rfd.results_id = r.id WHERE r.state = 'CURRENT_BLUEPRINT' ) SELECT rfd.device_id, rfd.results_id, r.test_name, r.results, r.state FROM results_for_device AS rfd INNER JOIN results AS r ON rfd.results_id = r.id INNER JOIN result_threads AS rt ON rfd.device_id = rt.device_id AND r.test_name = rt.test_name AND r.id >= rt.results_id ;
device_id results_id test_name results state
1 1 test1 {} CURRENT_BLUEPRINT
1 2 test1 {} MATCHING_BLUEPRINT
1 3 test1 {} MATCHING_BLUEPRINT
1 4 test1 {} NOT_MATCHING_BLUEPRINT
2 7 test1 {} CURRENT_BLUEPRINT
2 8 test1 {} MATCHING_BLUEPRINT
2 9 test1 {} MATCHING_BLUEPRINT
1 12 test2 {} CURRENT_BLUEPRINT
1 13 test2 {} MATCHING_BLUEPRINT
1 14 test2 {} MATCHING_BLUEPRINT
2 16 test2 {} CURRENT_BLUEPRINT
2 17 test2 {} MATCHING_BLUEPRINT
2 18 test2 {} MATCHING_BLUEPRINT
2 19 test2 {} NOT_MATCHING_BLUEPRINT
 hidden batch(es)


SELECT device_id, results_id, test_name, results, state FROM ( SELECT rfd.device_id, rfd.results_id, r.test_name, r.results, r.state, MAX(r.id) FILTER (WHERE r.state = 'CURRENT_BLUEPRINT') OVER w AS this_id, MAX(r.id) OVER w AS max_id FROM results_for_device AS rfd INNER JOIN results AS r ON rfd.results_id = r.id WINDOW w AS (PARTITION BY rfd.device_id, r.test_name) ) AS derived WHERE results_id BETWEEN this_id AND max_id ORDER BY results_id ASC ;
device_id results_id test_name results state
1 1 test1 {} CURRENT_BLUEPRINT
1 2 test1 {} MATCHING_BLUEPRINT
1 3 test1 {} MATCHING_BLUEPRINT
1 4 test1 {} NOT_MATCHING_BLUEPRINT
2 7 test1 {} CURRENT_BLUEPRINT
2 8 test1 {} MATCHING_BLUEPRINT
2 9 test1 {} MATCHING_BLUEPRINT
1 12 test2 {} CURRENT_BLUEPRINT
1 13 test2 {} MATCHING_BLUEPRINT
1 14 test2 {} MATCHING_BLUEPRINT
2 16 test2 {} CURRENT_BLUEPRINT
2 17 test2 {} MATCHING_BLUEPRINT
2 18 test2 {} MATCHING_BLUEPRINT
2 19 test2 {} NOT_MATCHING_BLUEPRINT
 hidden batch(es)