Re: Extract only maximum date from column - Mailing list pgsql-general

On Thu, 4 Dec 2025, Adrian Klaver wrote:

> Would the below work?:
>
> WITH lc AS (SELECT person_nbr, max(next_contact) AS last_contact from 
> contacts where next_contact > '2025-11-01' group by c.person_nbr)
> select p.person_nbr, p.company_nbr, lc.last_contact from people AS p join lc 
> on p.person.nbr = lc.person_nbr;

Adrian,

Reformated and still has an error:
WITH lc AS (SELECT person_nbr, max(next_contact) AS last_contact
      from contacts where next_contact >= '2025-11-01'
      group by c.person_nbr)
select p.person_nbr, p.company_nbr, lc.last_contact
from people AS p
join lc on p.person.nbr = lc.person_nbr;

psql:companies-contacted-2025.sql:16: ERROR:  missing FROM-clause entry for table "c"
LINE 3:      group by c.person_nbr)

So, tweaking from reported errors:
WITH lc AS (SELECT p.person_nbr, max(c.next_contact) AS last_contact
      from people as p, contacts as c
      where next_contact >= '2025-11-01'
      group by p.person_nbr)
select p.person_nbr, p.company_nbr, lc.last_contact
from people AS p
join lc on p.person.nbr = lc.person_nbr;

psql:companies-contacted-2025.sql:9: ERROR:  missing FROM-clause entry for table "person"
LINE 7: join lc on p.person.nbr = lc.person_nbr;
                    ^
This is obviously a much more complicated query than I expected.

Thanks,

Rich