0

I have two tables: A and B

A

provider_id date
111 date
222 date
333 date

B

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
srgbnd
  • 5,404
  • 9
  • 44
  • 80
  • Not certain if this is a duplicate: https://stackoverflow.com/questions/1124603/grouped-limit-in-postgresql-show-the-first-n-rows-for-each-group – MSalters Aug 18 '22 at 13:45

2 Answers2

2

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
Stefanov.sm
  • 11,215
  • 2
  • 21
  • 21
0

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;
srgbnd
  • 5,404
  • 9
  • 44
  • 80