Re: Subquery to select max(date) value - Mailing list pgsql-general

On Tue, 12 Feb 2019, Ken Tanzer wrote:

> 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 ...


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.

Thanks,

Rich