MIME-Version: 1.0
In-Reply-To: <759515ff-7393-d28f-d627-6f4a48c11f4@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> <CAKFQuwbzjjBi7YV1gSn=2jX7h-FK8kf8fbpWmpYqX-8pSD7beA@mail.gmail.com>
 <759515ff-7393-d28f-d627-6f4a48c11f4@appl-ecosys.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Thu, 4 Dec 2025 15:29:04 -0700
Message-ID: <CAKFQuwaTe2vX30erug=2v0G5BL3G=vYvwNC-8jJFkKUe3EimEQ@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="000000000000495a05064527dbf1"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwaTe2vX30erug%3D2v0G5BL3G%3DvYvwNC-8jJFkKUe3EimEQ%40mail.gmail.com>
Precedence: bulk

--000000000000495a05064527dbf1
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:
>
> As mentioned, the aggregate max should be avoided - you aren=E2=80=99t do=
ing
>> statistics, you are ranking.
>>
>
> David,
>
> Got it.
>
> 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;
>>
>
> Select person.*, lastcontact.*
> from people
> join lateral (select contact.*
>      from contacts
>      where contacts.person_nbr =3D 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 =3D 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
>
>

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

David J.

--000000000000495a05064527dbf1
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">
As mentioned, the aggregate max should be avoided - you aren=E2=80=99t doin=
g<br>
statistics, you are ranking.<br>
</blockquote>
<br>
David,<br>
<br>
Got it.<br>
<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">
Select person.*, lastcontact.* from person join lateral (select contact.*<b=
r>
from contact where contact.person_id=3Dperson.perso<wbr>n_id order by<br>
last_contact_date desc limit 1) as lastcontact on true;<br>
</blockquote>
<br>
Select person.*, lastcontact.*<br>
from people<br>
join lateral (select contact.*<br>
=C2=A0 =C2=A0 =C2=A0from contacts<br>
=C2=A0 =C2=A0 =C2=A0where contacts.person_nbr =3D people.person_nbr<br>
=C2=A0 =C2=A0 =C2=A0order by last_contact_date<br>
=C2=A0 =C2=A0 =C2=A0desc limit 1)<br>
as lastcontact on true;<br>
<br>
psql:companies-contacted-2025.<wbr>sql:10: ERROR:=C2=A0 missing FROM-clause=
 entry for table &quot;contact&quot;<br>
LINE 3: join lateral (select contact.*<br>
<br>
So:<br>
Select person.*, lastcontact.*<br>
from people<br>
join lateral (select contacts.*<br>
=C2=A0 =C2=A0 =C2=A0from contacts<br>
=C2=A0 =C2=A0 =C2=A0where contacts.person_nbr =3D people.person_nbr<br>
=C2=A0 =C2=A0 =C2=A0order by last_contact_date<br>
=C2=A0 =C2=A0 =C2=A0desc limit 1)<br>
as lastcontact on true;<br>
<br>
psql:companies-contacted-2025.<wbr>sql:10: ERROR:=C2=A0 column &quot;last_c=
ontact_date&quot; does not exist<br>
LINE 6:=C2=A0 =C2=A0 =C2=A0 order by last_contact_date<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0<br>
</blockquote><div><br></div><div>I was giving you a query form.=C2=A0 You s=
hould use the actual table and column names in your schema=E2=80=A6</div><d=
iv><br></div><div>David J.</div><div>=C2=A0</div>

--000000000000495a05064527dbf1--