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

Reply via email to