The query is to return the latest next_contact date for each person. Using
the max() aggregate function and modeling the example of lo_temp on page 13
of the rel. 10 manual I wrote this statement:
select (P.person_id, P.lname, P.fname, P.direct_phone, O.org_name, 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 A)
group by A.next_contact, P.person_id;
The syntax error returned by psql is:
psql:next_contact_date.sql:7: ERROR: syntax error at or near "select"
LINE 4: A.next_contact = select (max(A.next_contact) from A)
^
and I fail to see what I've done incorrectly.
Do I need to insert DISTINCT ON in the main or sub query? If so, what is the
correct syntax to extract all desired columns from each selected row?
If this is covered in the manual please point me to the proper section; if
not, please educate me on the appropriate syntax to produce the desired
output.
TIA,
Rich
