Re: Subquery to select max(date) value

>>>>> "Adrian" == Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> writes:

Adrian> Close to your last posted query. person_id 2 and 3 have NULL
Adrian> values for activities data as there is no record for 2 and 3 is
Adrian> out of the date range.:

Adrian> select
Adrian> p.person_id,
Adrian> p.desc_fld,
Adrian> a.next_contact
Adrian> from
Adrian> people as p
Adrian> LEFT JOIN (
Adrian> SELECT
Adrian> DISTINCT ON (person_id)
[...]
Adrian> ) a USING (person_id)
Adrian> ;

DISTINCT ON with no matching ORDER BY at the _same_ query level is
non-deterministic.

Also DISTINCT ON isn't efficient. Consider instead something along the
lines of:

select p.*,
a.* -- for illustration
from people p
join lateral (select *
from activities a1
where a1.person_id = p.person_id
and a1.next_contact > '2018-12-31'
and a1.next_contact <= 'today'
order by a1.next_contact desc
limit 1) a
on true;

(make sure to have an index on activities(person_id,next_contact))

--
Andrew (irc:RhodiumToad)