From: | Rich Shepard <rshepard(at)appl-ecosys(dot)com> |
---|---|
To: | "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Subquery to select max(date) value |
Date: | 2019-02-13 14:28:18 |
Message-ID: | alpine.LNX.2.20.1902130623150.10544@salmo.appl-ecosys.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2019-02-13 14:48:10 | Re: Subquery to select max(date) value |
Previous Message | Joe Conway | 2019-02-13 14:17:31 | Re: SV: SV: Implementing pgaudit extension on Microsoft Windows |