How to join two tables A and B and have the most recent status from B?-CodePudding

I have two tables: A and B

provider_id	date
111	date
222	date
333	date

provider_id	status	date
111	initialized	date
111	released	date
222	failed	date

The result I want

provider_id	status	date
111	released	A date
222	failed	A date
333	null	A date

Among the things I tried is the left join

select * from "A" left join "B" on "B"."provider_id" = "A"."provider_id" order by "A"."date" desc;

But I got the duplicated records based on status

provider_id	status	date
111	initialized	date
111	released	date
222	failed	date
333	null	date

CodePudding user response：

Use distinct on to retrieve only one record per provider_id and order by to specify that this shall be the most recent (latest) one. More info

select distinct on (provider_id)
    provider_id, status, "B"."date" 
from "A" left join "B" using (provider_id)
order by provider_id, "B"."date" desc;

provider_id	status	date
111	released	2022-01-03
222	failed	2022-01-02
333

CodePudding user response：

To sort on the A date, you can join B with a subquery

select * from "A" left join (
    select distinct on ("provider_id") * from "B" order by "provider_id", "date" desc
) B using ("provider_id") order by "A"."date" desc;