Content-Type: multipart/alternative;
 boundary="------------9sVkhunWwu5ic09hK4akzTTG"
Message-ID: <d0606565-1fad-419d-922c-b3e8f76495a6@gmail.com>
Date: Thu, 4 Dec 2025 15:01:46 -0500
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Extract only maximum date from column
To: pgsql-general@lists.postgresql.org
References: <db81f086-e1a5-5798-f9bd-5bed5b1bc7f4@appl-ecosys.com>
From: Bryan Sayer <blslists@gmail.com>
Content-Language: en-US
In-Reply-To: <db81f086-e1a5-5798-f9bd-5bed5b1bc7f4@appl-ecosys.com>
Archived-At: <https://www.postgresql.org/message-id/d0606565-1fad-419d-922c-b3e8f76495a6%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------9sVkhunWwu5ic09hK4akzTTG
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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*/
On 12/4/2025 2:55 PM, Rich Shepard 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 >= '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
>
>
--------------9sVkhunWwu5ic09hK4akzTTG
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>I believe in general you need</p>
    <p>having c.next_contact = max(c.next_contact)</p>
    <p>(at least in ANSI SQL) Use having for after the join has occured</p>
    <div class="moz-signature"><b>Bryan Sayer</b><br>
      Retired Demographer/Statistician<br>
      <i>In a world in which you can be anything, <b>be kind</b></i>
      <br>
    </div>
    <div class="moz-cite-prefix">On 12/4/2025 2:55 PM, Rich Shepard
      wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:db81f086-e1a5-5798-f9bd-5bed5b1bc7f4@appl-ecosys.com">I
      want the script to extract only the maximum `next_contact' date
      and
      <br>
      haven'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;= '2025-11-01'
      <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>
      <br>
      TIA,
      <br>
      <br>
      Rich
      <br>
      <br>
      <br>
    </blockquote>
  </body>
</html>

--------------9sVkhunWwu5ic09hK4akzTTG--