CREATE TABLE table1 (id int, time timestamp);

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');

CREATE TABLE table2 (id int, time timestamp);

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

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 ;