>>>>> "Adrian" == Adrian Klaver <adrian.klaver@aklaver.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)