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
ona.p_id = p.id;
In my experience joining with the result of the distinct on () is quicker then applying the distinct on () on the
resultof the join.