Re: Subquery to select max(date) value - Mailing list pgsql-general
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
(253) 245-3801
learn more about AGENCY or
follow the discussion.
pgsql-general by date: