Re: Subquery to select max(date) value

On Fri, 15 Feb 2019, Andrew Gierth wrote:

> Rich> I've not before run 'explain' on a query. Would that be
> Rich> appropriate here?
>
> Yes.

Andrew,

I'll learn how to use it.

> 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.

Got it now.

> 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.

This puts everything in perspective and is a very valuable lesson for me as
this application has many queries of this type. You've put together all that
I've read in the manual, on this mail list thread, and on web pages. I
really appreciate your patient guidance.

Best regards,

Rich