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 |