Re: Extract only maximum date from column

[Rich Shepard]

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