I have two tables in my PostgreSQL database (Table A and Table B). Both of these tables have a createdAt column. I want to do a full join on these two tables and then sort the result based on createdAt values on both A and B tables. Below is an example of what I want to be my query result.
Table A
colA joinColumnA createdAtA
----- ----------- ---------
a1 1 2014
a2 2 2019
a3 3 2020
Table B
colB, joinColumnB createdAtB
--- ---------- -----------
b1 2 2013
b2 4 2015
b3 5 2016
Result
colA, joinColumnA createdAtA colB joinColumnB createdAtB
---- ----------- ----------- ---- ----------- -----------
a3 3 2020 null null null
a2 2 2019 b1 2 2013
null null null b3 5 2016
null null null b2 4 2015
a1 1 2014 null null null
Advertisement
Answer
You can ORDER BY GREATEST(createdAtA, createdAtB):
SELECT *
FROM tableA
FULL JOIN tableB
ON tableA."joinColumnA" = tableB."joinColumnB"
ORDER BY GREATEST("createdAtA", "createdAtB") DESC;
| colA | joinColumnA | createdAtA | colB | joinColumnB | createdAtB |
|---|---|---|---|---|---|
| a3 | 3 | 2020 | |||
| a2 | 2 | 2019 | b1 | 2 | 2013 |
| b3 | 5 | 2016 | |||
| b2 | 4 | 2015 | |||
| a1 | 1 | 2014 |