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?

In general your query seems OK to me. But if you can add sample data from both table with exact expected output, this would help more. — mkRabbani, Sep 01 '19 at 09:25

clemens · Accepted Answer · 2019-09-01T09:34:40.197

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.

score 2 · Answer 2 · answered Sep 01 '19 at 09:52

Your query looks okay to me. One typical way to join only n rows by some order from the other table is a lateral join:

SELECT *
FROM contracts c
CROSS JOIN LATERAL
(
  SELECT *
  FROM contract_descriptions cd
  WHERE cd.contract_id = c.contract_id
  ORDER BY cd.date_description DESC
  FETCH FIRST 1 ROW ONLY
) cdlast;

SQL left join on maximum date

2 Answers2