Re: Subquery to select max(date) value

>>>>> "Rich" == Rich Shepard <rshepard@appl-ecosys.com> writes:

 Rich> I found a couple of web pages describing the lateral join yet
 Rich> have not correctly applied them. The manual's page did not help
 Rich> me get the correct syntax, either. Think I'm close, however:

 Rich> select p.person_id, p.lname, p.fname, p.direct_phone, o.org_name, a.next_contact
 Rich> from people as p, organizations as o
 Rich>     lateral
 Rich>         (select a.next_contact

LATERAL (SELECT ...)   is syntactically like (SELECT ...) in that it
comes _after_ a "," in the from-clause or after a [LEFT] JOIN keyword.
Don't think of LATERAL as being a type of join, think of it as
qualifying the (SELECT ...) that follows.

 Rich>         from activities as a
 Rich>         where a.next_contact is not null and a.next_contact <= 'today' and
 Rich>               a.next_contact > '2018-12-31'

You'd want a condition here that references the "people" table; the
whole point of LATERAL is that it opens up the scope of column
references in the subquery to include those tables which are to its left
in the from-clause.

 Rich>         order by person_id,next_contact);

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.

-- 
Andrew (irc:RhodiumToad)