> On 4 Dec 2025, at 20:55, 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
> wh
On 12/4/25 14:17, Rich Shepard wrote:
On Thu, 4 Dec 2025, Adrian Klaver wrote:
So, tweaking from reported errors:
WITH lc AS (SELECT p.person_nbr, max(c.next_contact) AS last_contact
from people as p, contacts as c
where next_contact >= '2025-11-01'
group by p.person_nbr)
se
On Thursday, December 4, 2025, Rich Shepard
wrote:
> On Thu, 4 Dec 2025, David G. Johnston wrote:
>
> As mentioned, the aggregate max should be avoided - you aren’t doing
>> statistics, you are ranking.
>>
>
> David,
>
> Got it.
>
> Select person.*, lastcontact.* from person join lateral (select
On Thu, 4 Dec 2025, David G. Johnston wrote:
As mentioned, the aggregate max should be avoided - you aren’t doing
statistics, you are ranking.
David,
Got it.
Select person.*, lastcontact.* from person join lateral (select contact.*
from contact where contact.person_id=person.person_id order
On Thu, 4 Dec 2025, Adrian Klaver wrote:
Would the below work?:
WITH lc AS (SELECT person_nbr, max(next_contact) AS last_contact from
contacts where next_contact > '2025-11-01' group by c.person_nbr)
select p.person_nbr, p.company_nbr, lc.last_contact from people AS p join lc
on p.person.nbr
On Thursday, December 4, 2025, Rich Shepard
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.
On 12/4/25 1:39 PM, Rich Shepard 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 windo
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
You don't include the where clause, just the having clause after the
group by.
At least that is what I remember. But it has been awhile.
*Bryan Sayer*
Retired Demographer/Statistician
/In a world in which you can be anything, *be kind*/
On 12/4/2025 3:13 PM, Rich Shepard wrote:
On Thu, 4 Dec 2
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
On Thu, 4 Dec 2025, Ron Johnson wrote:
May not be the only way, but it's how I do it:
SELECT *
FROM foo
WHERE some_dt = (SELECT MAX(some_dt) FROM foo);
It might return more than one row...
Ron,
Didn't quite work for me this way:
select p.person_nbr, p.company_nbr, c.next_contact
from people
On Thu, 4 Dec 2025, Bryan Sayer wrote:
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 occurred
Bryan,
Postgresql didn't like that regardless of where I inserted the `having'
stanza.
Thanks,
Rich
On Thursday, December 4, 2025, 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
> wher
On Thu, Dec 4, 2025 at 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
> wher
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 sc
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_
On Thu, Dec 4, 2025 at 2:00 AM BUBACZ Martin wrote:
> Hi All,
>
>
>
> I’m fairly new to postgresql and I have a question concerning upgrading a
> 2 node failover physical replication cluster. I’m automating the cluster
> installation and maintenance by using Puppet. All runs good so far.
>
>
>
>
17 matches
Mail list logo
