Re: Subquery to select max(date) value - Mailing list pgsql-general

>>>>> "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)