Select the first row of a LEFT JOIN

Question

I'm trying to do a left join. But I only want the first row of the joined table.

When I do :

SELECT DISTINCT
  c.reference
FROM contracts as c

output : 7400 rows

But when I try to do the left join I have a lot of duplicates.

I already tried to only get the first row but it does not work. Here is my code :

SELECT DISTINCT
  c.reference,
  contract_premiums.start_date
FROM contracts as c
LEFT OUTER JOIN contract_premiums ON contract_premiums.contract_id=(
  SELECT contract_id FROM contract_premiums
  WHERE contract_premiums.contract_id = c.id
  ORDER BY contract_premiums.created_at ASC 
  LIMIT 1)

output : 11500 rows

---

Note the database in Postgresql and I'm using this request in klipfolio.

Answer 1

If you just want the latest start_date per reference, you can use aggregation:

select c.reference, max(cp.start_date) max_start_date
from contracts c
left join contracts_premiums cp on cp.contract_id = c.id
group by c.reference

This guarantees that you will only get one row per reference.

If you want more columns from contracts_premiums, or if you want to sort by a column other than start_date (possibly, you want created_at instead), then another option is distinct on:

select distinct on (c.reference) c.reference, cp.start_date, cp.created_at
from contracts c
left join contracts_premiums cp on cp.contract_id = c.cid
order by c.reference, cp.created_at desc