0

I have table applications and assets. Assets belong to applications. I want to get the highest last modified date and also the adjacent value (of who that value belongs to).

Application table

| application_id | name    |
| -------------- | --------|
| 101            | Weather |
| 102            | HR      |

Asset table

| asset_id | last_modified | last_modified_by | application_id |
| -------- | ------------- | -----------------| -------------- |
| 1        | 2013-01-20    | Toby             | 101            |
| 2        | 2014-03-25    | John             | 102            |
| 3        | 2015-05-30    | Mike             | 101            |
| 4        | 2016-06-25    | Toby             | 101            |
| 5        | 2017-07-26    | Mike             | 101            |
| 6        | 2018-08-27    | Lucky            | 102            |

I want

| application_id | name    | last_modified | last_modified_by |
| -------------- | --------| --------------| -----------------|
| 101            | Weather | 2017-07-26    | Mike             |
| 102            | HR      | 2018-08-27    | Lucky            |

Getting the last_modified is easy but I want to get the adjacent last_modified_by field as well.

  • @a_horse_with_no_name I modified the question to include tables and desired output – Bryan Jun 24 at 6:38
1

This it a variation of a problem.

I would do this with a join to a derived table:

select *
from application app
  join (
    select distinct on (application_id) *
    from asset 
    order by application_id, last_modified desc
  ) ast on ast.application_id = app.id;
| improve this answer | |
1

I would use DISTINCT ON:

SELECT DISTINCT ON (application_id) *
FROM application
   JOIN asset USING (application_id)
ORDER BY application_id, asset.last_modified_by DESC;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.