MIME-Version: 1.0
References: <db81f086-e1a5-5798-f9bd-5bed5b1bc7f4@appl-ecosys.com>
In-Reply-To: <db81f086-e1a5-5798-f9bd-5bed5b1bc7f4@appl-ecosys.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Thu, 4 Dec 2025 15:02:04 -0500
Message-ID: <CANzqJaC9+koXCtuqKFxqZdsyHEk7J3QxOqF3UAwC0W2dEi-6sA@mail.gmail.com>
Subject: Re: Extract only maximum date from column
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000030c736064525ceef"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaC9%2BkoXCtuqKFxqZdsyHEk7J3QxOqF3UAwC0W2dEi-6sA%40mail.gmail.com>
Precedence: bulk

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

On Thu, Dec 4, 2025 at 2:55=E2=80=AFPM Rich Shepard <rshepard@appl-ecosys.c=
om>
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?
>

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

It might return more than one row...

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

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Dec 4, 2025 at 2:55=E2=80=AFPM Ri=
ch Shepard &lt;<a href=3D"mailto:rshepard@appl-ecosys.com">rshepard@appl-ec=
osys.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_containe=
r"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bord=
er-left:1px solid rgb(204,204,204);padding-left:1ex">I want the script to e=
xtract 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>M=
ay not be the only way, but it&#39;s how I do it:</div><div><font face=3D"m=
onospace">SELECT *=C2=A0</font></div><div><font face=3D"monospace">FROM foo=
</font></div><div><font face=3D"monospace">WHERE some_dt =3D (SELECT MAX(so=
me_dt) FROM foo);</font></div><div><br></div><div>It might return more than=
 one row...</div><div><br></div></div><span class=3D"gmail_signature_prefix=
">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"=
>Death to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;=
m still alive.<br><div><div>&lt;Redacted&gt; lobster!</div></div></div></di=
v></div></div>

--00000000000030c736064525ceef--