How do I get the adjacent value of MAX/MIN aggregate function?
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;