Re: Most recent row

David G. Johnston schrieb am 05.05.2017 um 17:14:
> ​I would start with something using DISTINCT ON and avoid redundant
> data. If performance starts to suck I would then probably add a field
> to people where you can record the most recent assessment id and
> which you would change via a trigger on assessments.
>
> (not tested)​
>
> ​SELECT DISTINCT ON (p) p, a
> FROM people p
> LEFT JOIN ​assessments a USING (p_id)
> ORDER BY p, a.as_timestamp DESC;
>
> David J.
>

I would probably put the evaluation of the "most recent assessment" into a derived table:

select *
from people p
join (
select distinct on (p_id) *
from assessments
order by p_id, as_timestamp desc
) a on a.p_id = p.id;

In my experience joining with the result of the distinct on () is quicker then applying the distinct on () on the result of the join.