Sql Joined By Last Date

This is a question asked here before more than once, however I couldn't find what I was looking for. I am looking for join two tables, where the joined table is set by the last reg

Solution 1:

DISTINCT ON or is your friend. Here is a solution with correct syntax:

SELECT a.id, b.updated, b.col1, b.col2
FROM   table_a as a
LEFT   JOIN (
   SELECTDISTINCTON (table_a_id)
          table_a_id, updated, col1, col2
   FROM   table_b
   ORDERBY table_a_id, updated DESC
   ) b ON a.id = b.table_a_id;

Or, to get the whole row from table_b:

SELECT a.id, b.*
FROM   table_a as a
LEFT   JOIN (
   SELECTDISTINCTON (table_a_id)
          *
   FROM   table_b
   ORDERBY table_a_id, updated DESC
   ) b ON a.id = b.table_a_id;

Detailed explanation for this technique as well as alternative solutions under this closely related question: Select first row in each GROUP BY group?

Solution 2:

You can use Postgres's distinct on syntax:

select a.id, b.*
from table_a as a left join
     (selectdistincton (table_a_id) table_a_id, . . . 
      from table_b
      orderby table_a_id, updated desc
     ) b
     on a.id = b.table_a_id

Where the . . . is, you should put in the columns that you want.