Join table based on date

Question

I have two tables:

Table A

ID | name
---+----------
 1 | example
 2 | example2

Table B (created field is timestamptz)

ID | id_table_a | dek  | created
---+------------+------+---------------------
 1 |    1       | deka | 2019-10-21 10:00:00
 2 |    2       | dekb | 2019-10-21 11:00:00
 3 |    1       | dekc | 2019-10-21 09:00:00
 4 |    2       | dekd | 2019-10-21 09:40:00
 5 |    1       | deke | 2019-10-21 09:21:00

I need to get records from Table A and each records should have the last dek from table b based on created.

How can I do that?

Answer 1

I would use a lateral join, very often this is faster than using a select max()

select a.*, b.dek
from table_a a
  join lateral (
    select id, id_table_a, dek, created
    from table_b 
    where b.id_table_a = a.id
    order by created desc 
    limit 1
  ) tb on true;

Another alternative is to use distinct on:

select a.*, b.dek
from table_a a
  join lateral (
    select distinct on (id_table_a) id, id_table_a, dek, created
    from table_b 
    order by id_table_a, created desc
  ) tb on tb.id_table_a = a.id;

It depends on your data distribution which one is faster.