Last record by date given a foreign table Consider this 2 tables:

CREATE TABLE massifs (
id uuid primary key,
name text unique);

CREATE TABLE records (
id uuid primary key,
massif_id uuid  not null references massif(id)
record_date timestamptz not null
some_usefulldata text)


I'm trying to get the last result for each massif. Something with a subquery ?

For example a result could be

massifs.name | record_date::date | some_usefulldata
mont-blanc   | 2020-02-10        | 10
aravis       | 2019-03-10        | 12


I think that the question is the same if I add this 2 tables : https://www.postgresql.org/docs/current/tutorial-fk.html and if I ask this question "give me the last weather records for each cities".
Ok, I finally got my Sh** together. Was a bit rusty :

SELECT result.* FROM massifs m
JOIN LATERAL (SELECT m.id, m.name, r.record_date, r.some_usefulldata 
    FROM records WHERE (m.id=r.massif_id) 
    ORDER BY r_record_date DESC LIMIT 1
) result ON true
ORDER BY m.id, result.record_date DESC


To be honest I found the answer on the timescaledb docs. I thought this was a common problem in TSDB, it was !