By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE tableA (user_id int, time_stamp int, order_id int);
CREATE TABLE tableB (user_id int, time_stamp int, order_id int);
INSERT INTO tableA VALUES
(1,20190101,100),
(2,20190103,201),
(3,20190102,300),
(5,20180209,99);
INSERT INTO tableB VALUES
(1,20190102,101),
(2,20190101,200),
(3,20190103,305),
(4,20190303,900);
4 rows affected
4 rows affected
with unionedTable as (
select * from tableA
union
select * from tableB)
,newerUsersTable as (
select distinct on (u.user_id)u.*
from unionedTable u
order by u.user_id, u.time_stamp desc
)select * from newerUsersTable
| user_id | time_stamp | order_id |
|---|---|---|
| 1 | 20190102 | 101 |
| 2 | 20190103 | 201 |
| 3 | 20190103 | 305 |
| 4 | 20190303 | 900 |
| 5 | 20180209 | 99 |