Re: Subquery to select max(date) value

>>>>> "Rich" == Rich Shepard <rshepard(at)appl-ecosys(dot)com> writes:

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

Yes.

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

The problem here is that you have no join conditions at all, so the
result set of this query is massive. And you've duplicated many tables
inside the subquery which is not necessary or appropriate.

select p.person_id, p.lname, p.fname, p.direct_phone, o.org_name, sq.*
from people as p
join organizations as o on p.organization_id=o.id -- OR WHATEVER
cross join
lateral (select a.next_contact
from activities as a
where a.person_id=p.person_id --VERY IMPORTANT
and a.next_contact > '2018-12-31'
and a.next_contact <= 'today'
and a.next_contact is not null
order by a.next_contact DESC
limit 1) sq;

Ordering by DESC with a limit 1 is used to get the max next_contact
value rather than the smallest; this is similar to max(), but makes it
trivial to also access the other columns of the _same_ activities row
which is being selected.

--
Andrew (irc:RhodiumToad)