Re: Subquery to select max(date) value

On Tue, Feb 12, 2019 at 2:48 PM Rich Shepard <rshepard(at)appl-ecosys(dot)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
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.