Re: [SQL] Most recent row - Mailing list pgsql-sql

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.