How do I get the adjacent value of MAX/MIN aggregate function? 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.
This it a variation of a greatest-n-per-group 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;