Re: Subquery to select max(date) value - Mailing list pgsql-general
On Tue, Feb 12, 2019 at 2:48 PM Rich Shepard <rshepard@appl-ecosys.com> wrote:
On Tue, 12 Feb 2019, Rich Shepard wrote:
> A.next_contact = (select (max(A.next_contact)) from Activities as A)
Errata:
The parentheses around the max aggregate are not necessary.
A.next_contact now displays at the end of each returned row as 'infinity'.
Your subquery isn't doing anything to match on person_id, so it's going to match all the records with the highest next_contact in activities.
I think you want something more like:
A.next_contact = (select (max(A.next_contact)) from Activities as A2 WHERE A2.person_id=A.person_id)
Or, for that matter, since next_contact is all that you're drawing from activities, you can also just put it in the select:
select (P.person_id, P.lname, P.fname, P.direct_phone, O.org_name,
(select max(A.next_contact) from Activities as A WHERE p.person_id=A.person_id)
FROM ...
Cheers,
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
pgsql-general by date: