CODEWITHSUNDEEP
sqlpostgresql
Ehsan Shekari
Ehsan Shekari

Reputation: 1002

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

Upvotes: 0

Views: 323

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133380

You could try using the union for max createdAT for join column in left joinwitn the two table adn order by ifnull(createdAtA, createdAtB)

    select colA, joinColumnA, createdAtA, null colB, null joinColumnB,  null createdAtB
    from (
        select joinColumn, max(createdAt)
        from (
            select joinColumnA joinColumn, createdAtA createdAt
            from tableA
            select joinColumnB , createdAtB
            from tableB
        ) t1
        group by joinColumn
    ) t2
    left join tableA ON tableA.joinColumnA = t2.joinColumn
    left join tableB ON tableB.joinColumnA = t2.joinColumn
    order by nullif(createdAtA, createdAtB)

Upvotes: 1

Marth
Marth

Reputation: 24812

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

Upvotes: 2

Related Questions