MIME-Version: 1.0
In-Reply-To: <db81f086-e1a5-5798-f9bd-5bed5b1bc7f4@appl-ecosys.com>
References: <db81f086-e1a5-5798-f9bd-5bed5b1bc7f4@appl-ecosys.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Thu, 4 Dec 2025 13:03:35 -0700
Message-ID: <CAKFQuwZEWkgQeWSB7nqzBjHS8kP7kvouT6jhKvfe_G2e3cJvOQ@mail.gmail.com>
Subject: Re: Extract only maximum date from column
To: Rich Shepard <rshepard@appl-ecosys.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000000033f7064525d361"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwZEWkgQeWSB7nqzBjHS8kP7kvouT6jhKvfe_G2e3cJvOQ%40mail.gmail.com>
Precedence: bulk

--0000000000000033f7064525d361
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thursday, December 4, 2025, Rich Shepard <rshepard@appl-ecosys.com>
wrote:

> 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 >=3D '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?
>

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 =E2=80=9Cjoin=E2=80=9D clauses to make that error le=
ss likely.

David J.

--0000000000000033f7064525d361
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thursday, December 4, 2025, Rich Shepard &lt;<a href=3D"mailto:rshepard@=
appl-ecosys.com">rshepard@appl-ecosys.com</a>&gt; wrote:<br><blockquote cla=
ss=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;pa=
dding-left:1ex">I want the script to extract only the maximum `next_contact=
&#39; date and<br>
haven&#39;t learned how to do this from my reading of web sites.<br>
<br>
The script:<br>
<br>
select p.person_nbr, p.company_nbr, c.next_contact<br>
from people as p, contacts as c<br>
where c.next_contact &gt;=3D &#39;2025-11-01&#39;<br>
group by p.person_nbr, p.company_nbr, c.next_contact<br>
order by p.person_nbr, p.company_nbr, max(c.next_contact);<br>
<br>
returns all contacts rather than only the latest one.<br>
<br>
Is using a sub-select the proper way?<br>
</blockquote><div><br></div><div>I would go with a lateral join subquery of=
 the contracts table.=C2=A0 Using an aggregates to perform ranking is an an=
ti-pattern. You want the contract ranked first when ordered by contract_dat=
e.=C2=A0 Either use a window function to explicitly rank the contracts or u=
se a limit/fetch clause to simply return the first ordered one.</div><div><=
br></div><div>You also seem to have completely missed joining people to con=
tracts - suggest using explicit =E2=80=9Cjoin=E2=80=9D clauses to make that=
 error less likely.</div><div><br></div><div>David J.</div><div><br></div>

--0000000000000033f7064525d361--