Re: Subquery to select max(date) value

On 2/13/19 2:24 PM, Rich Shepard wrote:
> On Wed, 13 Feb 2019, Andrew Gierth wrote:
>
>> Adrian> Close to your last posted query. person_id 2 and 3 have NULL
>> Adrian> values for activities data as there is no record for 2 and 3 is
>> Adrian> out of the date range.:
>
>> DISTINCT ON with no matching ORDER BY at the _same_ query level is
>> non-deterministic.
>>
>> Also DISTINCT ON isn't efficient. Consider instead something along the
>> lines of:
>
> Andrew/Adrian,
>
> I again read about DISTINCT and DISTINCT ON and fully understand them. I've
> also again read about JOINs; I understand them in terms of sets and _think_
> that in this query the people table is the LEFT (many) while the
> organizations and activities tables are the RIGHT (one) in the many-to-one
> relationships. That is, for each person_id there is only one org_id and
> only
> one next_contact that meets the three constraints.

Given a sufficiently large date range that may not be true as you may
have contacted a given person multiple times during that range and
generated multiple activities records.

>
> I'm now working on understanding how the syntax in the examples you two,
> Ken, and others have provided expresses the many-to-one relationships of
> organization and activities to people. I have the syntax that returns the
> next_date meeting the WHERE constraints to each person_id and am now
> focusing on adding the additional people and organization columns to the
> results. Might not be until tomorrow or Friday but I'll let you and the
> list
> subscribes know when I have understood all your suggestions and get the
> results I want from the query.
>
> Thanks again,
>
> Rich
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com