Skip to content
Advertisement

Sort full join based on two columns on two different tables

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

View on DB Fiddle

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement