Subquery to select max(date) value

Поиск
Список
Период
Сортировка
От Rich Shepard
Тема Subquery to select max(date) value
Дата
Msg-id alpine.LNX.2.20.1902121414490.15295@salmo.appl-ecosys.com
обсуждение исходный текст
Ответы Re: Subquery to select max(date) value  ("David G. Johnston" <david.g.johnston@gmail.com>)
Re: Subquery to select max(date) value  (Matt Zagrabelny <mzagrabe@d.umn.edu>)
Re: Subquery to select max(date) value  (Jan Kohnert <nospam001-lists@jan-kohnert.de>)
Список 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


В списке pgsql-general по дате отправления:

Предыдущее
От: github kran
Дата:
Сообщение: Re: Postgresql RDS DB Latency Chossing Hash join Plan
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: Subquery to select max(date) value