Subquery to select max(date) value

From: Rich Shepard <rshepard(at)appl-ecosys(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Subquery to select max(date) value
Date: 2019-02-12 22:23:49
Message-ID: alpine.LNX.2.20.1902121414490.15295@salmo.appl-ecosys.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2019-02-12 22:29:14 Re: Subquery to select max(date) value
Previous Message github kran 2019-02-12 18:55:46 Re: Postgresql RDS DB Latency Chossing Hash join Plan