SQL left join on maximum date

Question

I have two tables: contracts and contract_descriptions.

On contract_descriptions there is a column named contract_id which is equal on contracts table records.

I am trying to join the latest record on contract_descriptions:

SELECT *
FROM contracts c
LEFT JOIN contract_descriptions d ON d.contract_id = c.contract_id
AND d.date_description =
  (SELECT MAX(date_description)
   FROM contract_descriptions t
   WHERE t.contract_id = c.contract_id)

It works, but is it the performant way to do it? Is there a way to avoid the second SELECT?

Answer 1

You could also alternatively use DISTINCT ON:

SELECT * FROM contracts c LEFT JOIN (
    SELECT DISTINCT ON (cd.contract_id) cd.* FROM contract_descriptions cd
    ORDER BY cd.contract_id, cd.date_description DESC
) d ON d.contract_id = c.contract_id

DISTINCT ON selects only one row per contract_id while the sort clause cd.date_description DESC ensures that it is always the last description.

Performance depends on many values (for example, table size). In any case, you should compare both approaches with EXPLAIN.