On Tue, Feb 12, 2019 at 2:48 PM Rich Shepard <rshep...@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 *http://agency-software.org/ <http://agency-software.org/>* *https://demo.agency-software.org/client <https://demo.agency-software.org/client>* ken.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing list <agency-general-requ...@lists.sourceforge.net?body=subscribe> to learn more about AGENCY or follow the discussion.
