sql - Postgresql: select MAX value from two joined tables -

i have 2 tables:

tbl_status:   id   status_code   month_digest_id   project_id  tbl_project   id   name  tbl_month_digest:   id   month_ts 

i have project. each project has status 0 or more months (stored in tbl_month_digest). given list of project ids need latest status object.

i having trouble doing this. in mysql able select join of tbl_month_digest , tbl_status , adding having tbl_month_digest.month_ts = max(tbl_month_digest.month_ts). postgres insists add tbl_month_digest.month_ts group by, not have desired effect.

is possible recent status list of projects in single sql query in postgresql?

example data , 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 , 91, get

project_id  latest_status 90          'late' 92          'on_track' 

i'm not sure why recent status 91 on_track. but, can want using window functions.

my guess don't need month_digest table, because ids in time order.

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

you can same thing join:

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

edit:

actually, in postgres, can use distinct on:

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

i'm not sure want unknown statuses. filters them out.