Re: Subquery to select max(date) value

>
>
> Ken,
>
> Yes, cheers indeed. A bit of thinking and re-organizing resulted in a
> working statement that's close to what I want:
>
> select (P.person_id, P.lname, P.fname, P.direct_phone, O.org_name,
> (select max(A.next_contact)))
> from People as P, Organizations as O, Activities as A
> where P.org_id = O.org_id and P.person_id = A.person_id and
> /*A.next_contact = (select max(A.next_contact) from Activities as
> A) and */
> A.next_contact <= 'today' and A.next_contact > '2018-12-31' and
> A.next_contact is not null
> group by A.next_contact, O.org_id, P.person_id;
>
> The two issues I now focus on resolving are the multiple rows per person
> rather than only the most recent and the date displayed at the end of each
> output row. DISTINCT ON will eliminate the first issue.
>
>
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)
;

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.