MIME-Version: 1.0
In-Reply-To: <54638724-615f-52f-29a6-74e2567b9599@appl-ecosys.com>
References: <db81f086-e1a5-5798-f9bd-5bed5b1bc7f4@appl-ecosys.com>
 <CAKFQuwZEWkgQeWSB7nqzBjHS8kP7kvouT6jhKvfe_G2e3cJvOQ@mail.gmail.com> <54638724-615f-52f-29a6-74e2567b9599@appl-ecosys.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Thu, 4 Dec 2025 15:13:11 -0700
Message-ID: <CAKFQuwbzjjBi7YV1gSn=2jX7h-FK8kf8fbpWmpYqX-8pSD7beA@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="000000000000814c2c064527a2a6"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwbzjjBi7YV1gSn%3D2jX7h-FK8kf8fbpWmpYqX-8pSD7beA%40mail.gmail.com>
Precedence: bulk

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

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

> On Thu, 4 Dec 2025, David G. Johnston wrote:
>
> 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.
>>
>
> 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 =3D c.person_nbr and
>      last_contact >=3D '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
>

As mentioned, the aggregate max should be avoided - you aren=E2=80=99t doin=
g
statistics, you are ranking.

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

David J.

--000000000000814c2c064527a2a6
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">On Thu, 4 Dec 2025, David G. Johnston wrote:<br>
<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">
I would go with a lateral join subquery of the contracts table. Using an<br=
>
aggregates to perform ranking is an anti-pattern. You want the contract<br>
ranked first when ordered by contract_date. Either use a window function<br=
>
to explicitly rank the contracts or use a limit/fetch clause to simply<br>
return the first ordered one.<br>
</blockquote>
<br>
David,<br>
<br>
I&#39;m closer, but still missing the proper syntax:<br>
<br>
select p.person_nbr, p.company_nbr, c.next_contact<br>
from people as p, contacts as c<br>
join lateral (select max(c.next_contact) as last_contact<br>
=C2=A0 =C2=A0 =C2=A0where p.person_nbr =3D c.person_nbr and<br>
=C2=A0 =C2=A0 =C2=A0last_contact &gt;=3D &#39;2025-11-01&#39;<br>
=C2=A0 =C2=A0 =C2=A0)<br>
=C2=A0 =C2=A0 =C2=A0c on true;<br>
<br>
resulting in:<br>
psql:companies-contacted-2025.<wbr>sql:9: ERROR:=C2=A0 aggregate functions =
are not allowed in FROM clause of their own query level<br>
LINE 3: join lateral (select max(c.next_contact) as last_contact<br>
</blockquote><div><br></div><div>As mentioned, the aggregate max should be =
avoided - you aren=E2=80=99t doing statistics, you are ranking.</div><div><=
br></div><div>Select person.*, lastcontact.* from person join lateral (sele=
ct contact.* from contact where contact.person_id=3Dperson.person_id =C2=A0=
order by last_contact_date desc limit 1) as lastcontact on true;</div><div>=
<br></div><div>David J.</div><div><br></div>

--000000000000814c2c064527a2a6--