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

CREATE TABLE table1 (id int, time timestamp);
 hidden batch(es)


INSERT INTO table1 (id, time) VALUES (1, '2018-05-15 09:00'), (1, '2018-05-15 09:10'), (1, '2018-05-15 09:20'), (2, '2018-05-15 09:30'), (2, '2018-05-15 09:40');
 hidden batch(es)


CREATE TABLE table2 (id int, time timestamp);
 hidden batch(es)


INSERT INTO table2 (id, time) VALUES (1, '2018-05-15 09:21'), (1, '2018-05-15 09:31'), (2, '2018-05-15 09:41');
 hidden batch(es)


WITH t1 AS ( SELECT id, time, row_number() OVER (PARTITION BY id ORDER BY time DESC) AS rn FROM table1 ), t2 AS ( SELECT id, time, row_number() OVER (PARTITION BY id ORDER BY time DESC) AS rn FROM table2 ) SELECT t1.id, CASE WHEN t2.time IS NULL OR t1.time >= t2.time THEN t1.time ELSE t2.time END AS time, CASE WHEN t2.time IS NULL OR t1.time >= t2.time THEN 'table1' ELSE 'table2' END AS "table" FROM t1 LEFT JOIN t2 ON t1.id = t2.id AND t1.rn = t2.rn ORDER BY id, t1.rn DESC ;
id time table
1 2018-05-15 09:00:00 table1
1 2018-05-15 09:21:00 table2
1 2018-05-15 09:31:00 table2
2 2018-05-15 09:30:00 table1
2 2018-05-15 09:41:00 table2
 hidden batch(es)