postgres - how to show most recent date when joining two tables?-CodePudding

I am joining two tables but I want the most recent date shown. Here are my tables:

Project_updates:
company_id  project_id  progress    "date"
    1   1   5   2020-02-02
    1   1   20  2020-02-03
    1   1   200 2021-11-29
    1   8   200 2021-11-29
    2   3   20  2020-02-03
    2   3   20  2020-02-04
    2   3   30  2020-02-04
    2   3   30  2020-02-05

Project details:

project_id  project_name    project_carbon_impact   description project_goal    company_transition_id
1   project1    25  We are going to convert 100 trucks to eletric   100 1
2   project1    25  We are going to convert 100 trucks to eletric   100 1
3   project1    250 We are going to convert 1000 trucks to eletric  1000    2
4   ProjectTest 1500    ProjectTest Description Here    2500    3
5   project5    250 We are going to convert 1000 trucks to eletric  1000    2
6   project6    250 None    1000    2
7   project7    250     1000    2
8   Test Project  Dev   20  Project Description 50  1
9   Solar building project  0   We will add solar panels to the buildings to reduce emissions.  100 1

I'm trying to write a query to get me the details for each project for each company and the most recent progress I tried something like this:

select  tpd.project_id, max(date) as max_date, tpu.progress 
from company.transition_project_details tpd  
JOIN company.transition_project_updates tpu ON (tpu.project_id = tpd.project_id)
where tpd.company_transition_id = 2
group by tpd.project_id, tpu.progress
order by max_date desc

I've tried this also :

select  *
from company.transition_project_details tpd, 

(select company_id, project_id, progress, date,
rank() over (partition by project_id order by date desc)
from company.transition_project_updates tpu 
where tpu.company_id =2 ) as project_updates

where project_updates.rank =1 
and tpd.company_transition_id = 2

but for both, I get multiple results for each project_id. My goal is to just get all projects for a specific company_id and then their most current progress.

CodePudding user response：

You can try this

SELECT DISTINCT ON (company_id, project_id)
       *
  FROM transition_projects_updates AS tpu
 INNER JOIN transition_project_details AS tpd
    ON tpd.project_id = tpu.project_id
 ORDER BY tpu.company_id, tpu.project_id, tpu."date" DESC