Re: Subquery to select max(date) value - Mailing list pgsql-general

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?

Regards,

Rich