Re: Subquery to select max(date) value

On 2/13/19 6:28 AM, Rich Shepard wrote:
> On Tue, 12 Feb 2019, Ken Tanzer wrote:
>
>> If that's getting you what you want, then great and more power to
>> you.  It
>> looks like you'll only get people who have a next_contact in your target
>> window there.  You might also consider something like this...
>>
>> select
>>    p.person_id,
>>    p.lname,
>>    p.fname,
>>    p.direct_phone,
>>    o.org_name,
>>    a.next_contact
>> from
>>    people as p
>>    LEFT JOIN organizations o USING (person_id)
>>    LEFT JOIN (
>>        SELECT
>>            DISTINCT ON (person_id)
>>            person_id,
>>            next_contact
>>        FROM activities a
>>        -- WHERE ???
>>        ORDER BY person_id,next_contact DESC
>>    ) a USING (person_id)
>> ;
>
> I modified this to restrict the time and ignore nulls by replacing the
> question marks:
>
> WHERE a.next_contact <= 'today' and a.next_contact > '2018-12-31' and
>       a.next_contact is not null
>
> For a reason I've not yet found, the last condition is not observed; i.e.,
> those rows with null next_contact dates appear in the results. Position in
> the sequence makes no difference. What might cause this?

The LEFT JOIN. There are rows in people for which there no records
coming from the sub-select on activities, so the row is 'padded' with
NULL values for the missing data.

>
> Regards,
>
> Rich
>
>
>
>

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