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
