Re: Subquery to select max(date) value

On Fri, 15 Feb 2019, Andrew Gierth wrote:

> LATERAL (SELECT ...) is syntactically like (SELECT ...) in that it
> comes _after_ a "," in the from-clause or after a [LEFT] JOIN keyword.

Andrew,

Yes, the missing ',' made a big difference.

> You'd want a condition here that references the "people" table;

Got it.

> and I'm guessing you want that ordered by next_contact alone, possibly
> with LIMIT 1 to get just the nearest following next_contact time.

Using LIMIT 1 produces only the first returned row. This statement (using
max() for next_contact) produces no error message, but also no results so I
killed the process after 30 seconds. Without a syntax error for guidance I
don't know how to proceed. I've not before run 'explain' on a query. Would
that be appropriate here?

select p.person_id, p.lname, p.fname, p.direct_phone, o.org_name
from people as p, organizations as o,
lateral
(select p.person_id, p.lname, p.fname, p.direct_phone, o.org_name,
max(a.next_contact)
from people as p, organizations as o, activities as a
where a.next_contact > '2018-12-31' and
a.next_contact <= 'today' and
a.next_contact is not null
group by p.person_id, o.org_name, a.next_contact
order by p.person_id, o.org_name, a.next_contact) sq;

Regards,

Rich