Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table1 (id text, colA int, colB int, colC int, colD int, colE int, createDate date);
INSERT INTO table1
VALUES ('a1', '1', '1', '1', '1', '1', '2017/01/01');

CREATE TABLE table2 (mainid text, colA int, colB int, createdate date);
INSERT INTO table2 VALUES
    ('a1', '2', '2', '2018/05/01'),
    ('a1', '3', '3', '2019/01/01'),
    ('a1', '4', '4', '2020/01/01');

CREATE TABLE table3 (mainid text, colA int, colB int, colC int, createDate date);
INSERT INTO table3 VALUES
    ('a1', '6', '6', '6', '2019/01/01'),
    ('a1', '7', '7', '7', '2020/01/01'),
    ('a1', '8', '8', '8', '2021/01/01');

CREATE TABLE table4 (mainid text, colA int, colE int, colC int, createDate date);
INSERT INTO table4 VALUES
    ('a1', '9', '9', '9', '2018/06/01'),
    ('a1', '10', '10', '10', '2017/01/01'),
    ('a1', '12', '12', '12', '2020/01/01');
1 rows affected

3 rows affected

3 rows affected

3 rows affected

-- stock SQL tools only
SELECT id
    , (array_agg(colA ORDER BY colA IS NULL, createDate DESC))[1] AS colA
    , (array_agg(colB ORDER BY colB IS NULL, createDate DESC))[1] AS colB
    , (array_agg(colC ORDER BY colC IS NULL, createDate DESC))[1] AS colC
    , (array_agg(colD ORDER BY colD IS NULL, createDate DESC))[1] AS colD
    , (array_agg(colE ORDER BY colE IS NULL, createDate DESC))[1] AS colE
FROM (
   SELECT      id, colA, colB, colC, colD, colE, createDate FROM table1 WHERE     id='a1'
   UNION ALL
   (SELECT mainid, colA, colB, NULL, NULL, NULL, createDate FROM table2 WHERE mainid='a1' AND createDate < '2018-07-01' ORDER BY createDate DESC LIMIT 1)
   UNION ALL
   (SELECT mainid, colA, colB, colC, NULL, NULL, createDate FROM table3 WHERE mainid='a1' AND createDate < '2018-07-01' ORDER BY createDate DESC LIMIT 1)
   UNION ALL
   (SELECT mainid, colA, NULL, colc, NULL, colE, createDate FROM table4 WHERE mainid='a1' AND createDate < '2018-07-01' ORDER BY createDate DESC LIMIT 1)
   ) sub
GROUP BY 1;


id
cola
colb
colc
cold
cole


a1
9
2
9
1
9


-- custom aggregate function
CREATE OR REPLACE FUNCTION first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS 'SELECT $1';
 
CREATE AGGREGATE FIRST (
        sfunc    = public.first_agg,
        basetype = anyelement,
        stype    = anyelement
);
SELECT id
     , first(colA) AS colA
     , first(colB) AS colB
     , first(colC) AS colC
     , first(colD) AS colD
     , first(colE) AS colE
FROM (
   SELECT      id, colA, colB, colC, colD, colE, createDate FROM table1 WHERE     id='a1'
   UNION ALL
   (SELECT mainid, colA, colB, NULL, NULL, NULL, createDate FROM table2 WHERE mainid='a1' AND createDate < '2018-07-01' ORDER BY createDate DESC LIMIT 1)
   UNION ALL
   (SELECT mainid, colA, colB, colC, NULL, NULL, createDate FROM table3 WHERE mainid='a1' AND createDate < '2018-07-01' ORDER BY createDate DESC LIMIT 1)
   UNION ALL
   (SELECT mainid, colA, NULL, colc, NULL, colE, createDate FROM table4 WHERE mainid='a1' AND createDate < '2018-07-01' ORDER BY createDate DESC LIMIT 1)
   ORDER BY createDate DESC
   ) sub
GROUP  BY 1;


id
cola
colb
colc
cold
cole


a1
9
2
9
1
9