Postgresql: select MAX value from two joined tables

Question

I have two tables:

tbl_status:
  id
  status_code
  month_digest_id
  project_id

tbl_project
  id
  name

tbl_month_digest:
  id
  month_ts

I have a project. Each project has a status for zero or more months (stored in tbl_month_digest). Given a list of project IDs I need to get the latest status object.

I am having trouble doing this. In MySQL I was be able to select from a join of tbl_month_digest and tbl_status and adding a having tbl_month_digest.month_ts = max(tbl_month_digest.month_ts). Postgres insists that I add tbl_month_digest.month_ts to a group by, which does not have the desired effect.

Is it possible to get the most recent status for a list of projects in a single SQL query in postgresql?

Example data and expected result:

tbl_month_digest:
id   month_ts
1    2014-05-01
2    2014-06-01
3    2014-07-01

tbl_project:
id    name
90    'Foundation'
91    'Testing'
92    'Examination'

tbl_status:
id  project_id  month_digest_id  status_code
1   90          1                'on_track'
2   90          2                'on_track'
3   90          3                'late'
4   91          1                'late'
5   91          2                'unknown'
6   91          3                'unknown'
7   92          1                'late'
8   92          2                'late'
9   92          3                'on_track'

Given project IDs 90 and 91, I would like to get

project_id  latest_status
90          'late'
92          'on_track'

Gordon Linoff · Accepted Answer · 2014-11-19 13:40:04Z

1

I'm not sure why the most recent status for 91 would be on_track. But, you can do what you want using window functions.

My guess is that you don't even need the month_digest table, because the ids would normally be in time order.

select s.project_id, s.status as latest_status
from (select s.*,
             row_number() over (partition by project_id order by month_digest_id desc) as seqnum
      from tbl_status s
      where project_id in (90, 91)
     ) 
where seqnnum = 1;

You can do basically the same thing with a join:

select s.project_id, s.status as latest_status
from (select s.*,
             row_number() over (partition by project_id order by md.month_ts desc) as seqnum
      from tbl_status s join
           tbl_month_digest md
           on s.month_digest_id = md.id
      where project_id in (90, 91)
     ) 
where seqnnum = 1;

EDIT:

Actually, in Postgres, you can use distinct on:

      select distinct on(s.project_id) s.project_id, s.status as latest_status
      from tbl_status s join
           tbl_month_digest md
           on s.month_digest_id = md.id
      where s.project_id in (90, 91) and s.status <> 'unknown'
      order by s.project_id, md.month_ts desc;

I'm not sure what you want to do with the unknown statuses. This just filters them out.

edited Nov 19, 2014 at 13:40

answered Nov 19, 2014 at 13:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Krystian Cybulski Over a year ago

Sorry. I meant to put '92' = 'on_track'. Due to details in the actual implementation, I cannot rely on the id of the month_digest as a proxy for the month_ts -- I need to look at the actual month.

Collectives™ on Stack Overflow

Postgresql: select MAX value from two joined tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related