postgresql - What's the proper SQL query to find a 'status change' before given date? -

i have table of logged 'status changes'. need find latest status change user, , if a) 'type' of status change (s.new_status_id), , b) greater 7 days old (s.change_date), include in results. current query returns second-to-latest status change given user, don't want -- want evaluate last one.

how can modify query include record if recent status change user?

query

select distinct on (s.applicant_id) s.applicant_id, a.full_name, a.email_address, u.first_name, s.new_status_id, s.change_date, a.applied_class         automated_responses_statuschangelogs s         inner join application_app on (a.id = s.applicant_id)         inner join accounts_siuser u on (s.person_who_modified_id = u.id)         now() - s.change_date > interval '7' day         , s.new_status_id in             (select current_status              application_status              status_phase_id = 'in flow'             )         order s.applicant_id, s.change_date desc, s.new_status_id, s.person_who_modified_id; 

you can use row_number() filter 1 entry per applicant:

select  *    (         select  row_number() on (partition applicant_id                                     order change_date desc) rn         ,       *            automated_responses_statuschangelogs         ) lc join    application_app  on      a.id = lc.applicant_id join    accounts_siuser u  on      lc.person_who_modified_id = u.id join    application_status stat on      lc.new_status_id = stat.current_status   lc.rn = 1         , stat.status_phase_id = 'in flow'         , lc.change_date < now() - interval '7' day