Question 115089 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to join two tables A and B and have the most recent status from B?

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
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    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;

  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search