Subject: Extract only maximum date from column

From: Rich Shepard <[email-masked]>
Date: Thu, 4 Dec 2025 11:55:08 -0800 (PST)

I want the script to extract only the maximum `next_contact' date and
haven't learned how to do this from my reading of web sites.

The script:

select p.person_nbr, p.company_nbr, c.next_contact
from people as p, contacts as c
where c.next_contact >= '2025-11-01'
group by p.person_nbr, p.company_nbr, c.next_contact
order by p.person_nbr, p.company_nbr, max(c.next_contact);

returns all contacts rather than only the latest one.

Is using a sub-select the proper way?

TIA,

Rich

---

From: Bryan Sayer <[email-masked]>
Date: Thu, 4 Dec 2025 15:01:46 -0500

I believe in general you need

having c.next_contact = max(c.next_contact)

(at least in ANSI SQL) Use having for after the join has occured

*Bryan Sayer*
Retired Demographer/Statistician
/In a world in which you can be anything, *be kind*/

---

From: Ron Johnson <[email-masked]>
Date: Thu, 4 Dec 2025 15:02:04 -0500

> Is using a sub-select the proper way?

May not be the only way, but it's how I do it:
SELECT *
FROM foo
WHERE some_dt = (SELECT MAX(some_dt) FROM foo);

It might return more than one row...

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 4 Dec 2025 13:03:35 -0700

> Is using a sub-select the proper way?

I would go with a lateral join subquery of the contracts table.  Using an
aggregates to perform ranking is an anti-pattern. You want the contract
ranked first when ordered by contract_date.  Either use a window function
to explicitly rank the contracts or use a limit/fetch clause to simply
return the first ordered one.

You also seem to have completely missed joining people to contracts -
suggest using explicit “join” clauses to make that error less likely.

David J.

---

From: Rich Shepard <[email-masked]>
Date: Thu, 4 Dec 2025 12:18:14 -0800 (PST)

> It might return more than one row...

Ron,

Didn't quite work for me this way:

select p.person_nbr, p.company_nbr, c.next_contact
from people as p, contacts as c
where c.next_contact (select max(c.next_contact) from c.contacts) >= '2025-11-01'
group by p.person_nbr, p.company_nbr
order by p.person_nbr, p.company_nbr;

Thanks,

Rich

---

From: Rich Shepard <[email-masked]>
Date: Thu, 4 Dec 2025 12:22:31 -0800 (PST)

> return the first ordered one.

David,

It's 'contacts' rather than 'contracts'; a minor typo.

I'll read on making a lateral join. Thanks.

> suggest using explicit “join” clauses to make that error less likely.

Oops! My bad. Fixed.

Regards,

Rich

---

From: Rich Shepard <[email-masked]>
Date: Thu, 4 Dec 2025 13:39:56 -0800 (PST)

David,

I'm closer, but still missing the proper syntax:

select p.person_nbr, p.company_nbr, c.next_contact
from people as p, contacts as c
join lateral (select max(c.next_contact) as last_contact
      where p.person_nbr = c.person_nbr and
      last_contact >= '2025-11-01'
      )
      c on true;

resulting in:
psql:companies-contacted-2025.sql:9: ERROR:  aggregate functions are not allowed in FROM clause of their own query level
LINE 3: join lateral (select max(c.next_contact) as last_contact

Regards,

Rich

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 4 Dec 2025 13:58:01 -0800

On 12/4/25 1:39 PM, Rich Shepard wrote:
> psql:companies-contacted-2025.sql:9: ERROR:  aggregate functions are not 
> allowed in FROM clause of their own query level

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 Klaver
[email-masked]

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 4 Dec 2025 15:13:11 -0700

> LINE 3: join lateral (select max(c.next_contact) as last_contact

As mentioned, the aggregate max should be avoided - you aren’t doing
statistics, you are ranking.

Select person.*, lastcontact.* from person join lateral (select contact.*
from contact where contact.person_id=person.person_id  order by
last_contact_date desc limit 1) as lastcontact on true;

David J.

---

From: Rich Shepard <[email-masked]>
Date: Thu, 4 Dec 2025 14:17:45 -0800 (PST)

On Thu, 4 Dec 2025, Adrian Klaver wrote:

> 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

---

From: Rich Shepard <[email-masked]>
Date: Thu, 4 Dec 2025 14:26:03 -0800 (PST)

David,

Got it.

> last_contact_date desc limit 1) as lastcontact on true;

Select person.*, lastcontact.*
from people
join lateral (select contact.*
      from contacts
      where contacts.person_nbr = people.person_nbr
      order by last_contact_date
      desc limit 1)
as lastcontact on true;

psql:companies-contacted-2025.sql:10: ERROR:  missing FROM-clause entry for table "contact"
LINE 3: join lateral (select contact.*

So:
Select person.*, lastcontact.*
from people
join lateral (select contacts.*
      from contacts
      where contacts.person_nbr = people.person_nbr
      order by last_contact_date
      desc limit 1)
as lastcontact on true;

psql:companies-contacted-2025.sql:10: ERROR:  column "last_contact_date" does not exist
LINE 6:      order by last_contact_date
                       ^
Regards,

Rich

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 4 Dec 2025 15:29:04 -0700

> LINE 6:      order by last_contact_date

I was giving you a query form.  You should use the actual table and column
names in your schema…

David J.

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 4 Dec 2025 14:33:23 -0800

> LINE 7: join lc on p.person.nbr = lc.person_nbr;

My mistake should be p.person_nbr

---

From: Alban Hertroys <[email-masked]>
Date: Thu, 4 Dec 2025 23:58:17 +0100

> On 4 Dec 2025, at 20:55, Rich Shepard <[email-masked]> wrote:
> 

That looks like a classical case for a correlated subquery with WHERE NOT EXISTS.

Something like:

select p.person_nbr, p.company_nbr, c.next_contact
from people as p
join contacts as c
on -- I’m really missing some kind of relation between p and c here, I think that’s relevant
where c.next_contact >= ‘2025-11-01’
-- make sure there’s no later contact
and not exists (
	select 1 -- the value is irrelevant, as long as there’s no later instance of a contact
	from contacts c2
	where c2.next_contact >= ‘2025-11-01’
	and c2.next_contact > c.next_contact
)

P.S. My mail-client tried to outsmart me in this reply (in no small part) and I just got back from the pub, so I can’t exactly guarantee correctness of the above, but the principal idea should be solid.

Alban Hertroys
--
There is always an exception to always.

---

From: Bernice Southey <[email-masked]>
Date: Fri, 5 Dec 2025 09:35:50 +0000

> returns all contacts rather than only the latest one.

DISTINCT ON might be what you're looking for. It's an extremely useful
feature in postgres and well worth understanding. Here's a nice
explanation, that's similar to your case:
https://www.geekytidbits.com/postgres-distinct-on/

select distinct on (p.person_nbr) p.person_nbr, p.company_nbr, c.next_contact
from people as p join contacts as c using (person_nbr)
where c.next_contact >= '2025-11-01' order by  p.person_nbr, c.next_contact;

Using the following test data:
create table people(person_nbr, company_nbr) as values (1, 1), (2, 1), (3, 2);

create table contacts(person_nbr, next_contact) as values
(1, '2025-10-31'::date), (1, '2025-11-01'), (1, '2025, 11, 02'),
(3, '2025-11-02'::date), (3, '2025-11-03');

Here's the results:
person_nbr | company_nbr | next_contact
------------+-------------+--------------
          1 |           1 | 2025-11-01
          3 |           2 | 2025-11-02

DISTINCT ON can use an index matching the ORDER BY.
create index on contacts(person_nbr, next_contact);

Add some data to make the index worth it to the planner:
insert into contacts select i, '2025-11-05'::date + j from
generate_series(4, 100) i, generate_series(1, 100) j;
analyze contacts;

Check the query plan:
explain select distinct on (p.person_nbr) ...

This is what you want to see:
         ->  Index Only Scan using
contacts_person_nbr_next_contact_idx on contacts c
               Index Cond: (next_contact >= '2025-11-01'::date)

Thanks, Bernice

---

From: Rich Shepard <[email-masked]>
Date: Fri, 5 Dec 2025 05:41:44 -0800 (PST)

David,

The two table names are 'people' and 'contacts', the columns are
'person_nbr' and 'last_contact.' I usually use aliases rather than the table
name to reference the column.

Rich