public inbox for [email protected]
help / color / mirror / Atom feedExtract only maximum date from column
16+ messages / 7 participants
[nested] [flat]
* Extract only maximum date from column
@ 2025-12-04 19:55 Rich Shepard <[email protected]>
2025-12-04 20:01 ` Re: Extract only maximum date from column Bryan Sayer <[email protected]>
2025-12-04 20:02 ` Re: Extract only maximum date from column Ron Johnson <[email protected]>
2025-12-04 20:03 ` Re: Extract only maximum date from column David G. Johnston <[email protected]>
2025-12-04 22:58 ` Re: Extract only maximum date from column Alban Hertroys <[email protected]>
2025-12-05 09:35 ` Re: Extract only maximum date from column Bernice Southey <[email protected]>
0 siblings, 5 replies; 16+ messages in thread
From: Rich Shepard @ 2025-12-04 19:55 UTC (permalink / raw)
To: pgsql-general
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Extract only maximum date from column
2025-12-04 19:55 Extract only maximum date from column Rich Shepard <[email protected]>
@ 2025-12-04 20:01 ` Bryan Sayer <[email protected]>
4 siblings, 0 replies; 16+ messages in thread
From: Bryan Sayer @ 2025-12-04 20:01 UTC (permalink / raw)
To: [email protected]
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
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Extract only maximum date from column
2025-12-04 19:55 Extract only maximum date from column Rich Shepard <[email protected]>
@ 2025-12-04 20:02 ` Ron Johnson <[email protected]>
2025-12-04 20:18 ` Re: Extract only maximum date from column Rich Shepard <[email protected]>
4 siblings, 1 reply; 16+ messages in thread
From: Ron Johnson @ 2025-12-04 20:02 UTC (permalink / raw)
To: pgsql-general
On Thu, Dec 4, 2025 at 2:55 PM Rich Shepard <[email protected]>
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?
>
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...
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Extract only maximum date from column
2025-12-04 19:55 Extract only maximum date from column Rich Shepard <[email protected]>
2025-12-04 20:02 ` Re: Extract only maximum date from column Ron Johnson <[email protected]>
@ 2025-12-04 20:18 ` Rich Shepard <[email protected]>
0 siblings, 0 replies; 16+ messages in thread
From: Rich Shepard @ 2025-12-04 20:18 UTC (permalink / raw)
To: pgsql-general
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 as p, contacts as c
where c.next_contact (select max(c.next_contact) from c.contacts) >= '2025-11-01'
group by p.person_nbr, p.company_nbr
order by p.person_nbr, p.company_nbr;
Thanks,
Rich
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Extract only maximum date from column
2025-12-04 19:55 Extract only maximum date from column Rich Shepard <[email protected]>
@ 2025-12-04 20:03 ` David G. Johnston <[email protected]>
2025-12-04 20:22 ` Re: Extract only maximum date from column Rich Shepard <[email protected]>
2025-12-04 21:39 ` Re: Extract only maximum date from column Rich Shepard <[email protected]>
4 siblings, 2 replies; 16+ messages in thread
From: David G. Johnston @ 2025-12-04 20:03 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; +Cc: pgsql-general
On Thursday, December 4, 2025, Rich Shepard <[email protected]>
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?
>
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.
You also seem to have completely missed joining people to contracts -
suggest using explicit “join” clauses to make that error less likely.
David J.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Extract only maximum date from column
2025-12-04 19:55 Extract only maximum date from column Rich Shepard <[email protected]>
2025-12-04 20:03 ` Re: Extract only maximum date from column David G. Johnston <[email protected]>
@ 2025-12-04 20:22 ` Rich Shepard <[email protected]>
1 sibling, 0 replies; 16+ messages in thread
From: Rich Shepard @ 2025-12-04 20:22 UTC (permalink / raw)
To: pgsql-general
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,
It's 'contacts' rather than 'contracts'; a minor typo.
I'll read on making a lateral join. Thanks.
> You also seem to have completely missed joining people to contracts -
> suggest using explicit “join” clauses to make that error less likely.
Oops! My bad. Fixed.
Regards,
Rich
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Extract only maximum date from column
2025-12-04 19:55 Extract only maximum date from column Rich Shepard <[email protected]>
2025-12-04 20:03 ` Re: Extract only maximum date from column David G. Johnston <[email protected]>
@ 2025-12-04 21:39 ` Rich Shepard <[email protected]>
2025-12-04 21:58 ` Re: Extract only maximum date from column Adrian Klaver <[email protected]>
2025-12-04 22:13 ` Re: Extract only maximum date from column David G. Johnston <[email protected]>
1 sibling, 2 replies; 16+ messages in thread
From: Rich Shepard @ 2025-12-04 21:39 UTC (permalink / raw)
To: pgsql-general
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 = c.person_nbr and
last_contact >= '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
Regards,
Rich
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Extract only maximum date from column
2025-12-04 19:55 Extract only maximum date from column Rich Shepard <[email protected]>
2025-12-04 20:03 ` Re: Extract only maximum date from column David G. Johnston <[email protected]>
2025-12-04 21:39 ` Re: Extract only maximum date from column Rich Shepard <[email protected]>
@ 2025-12-04 21:58 ` Adrian Klaver <[email protected]>
2025-12-04 22:17 ` Re: Extract only maximum date from column Rich Shepard <[email protected]>
1 sibling, 1 reply; 16+ messages in thread
From: Adrian Klaver @ 2025-12-04 21:58 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; pgsql-general
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 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 = c.person_nbr and
> last_contact >= '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
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 = lc.person_nbr;
>
> Regards,
>
> Rich
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Extract only maximum date from column
2025-12-04 19:55 Extract only maximum date from column Rich Shepard <[email protected]>
2025-12-04 20:03 ` Re: Extract only maximum date from column David G. Johnston <[email protected]>
2025-12-04 21:39 ` Re: Extract only maximum date from column Rich Shepard <[email protected]>
2025-12-04 21:58 ` Re: Extract only maximum date from column Adrian Klaver <[email protected]>
@ 2025-12-04 22:17 ` Rich Shepard <[email protected]>
2025-12-04 22:33 ` Re: Extract only maximum date from column Adrian Klaver <[email protected]>
0 siblings, 1 reply; 16+ messages in thread
From: Rich Shepard @ 2025-12-04 22:17 UTC (permalink / raw)
To: pgsql-general
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 = lc.person_nbr;
Adrian,
Reformated and still has an error:
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 = lc.person_nbr;
psql:companies-contacted-2025.sql:16: ERROR: missing FROM-clause entry for table "c"
LINE 3: group by c.person_nbr)
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)
select p.person_nbr, p.company_nbr, lc.last_contact
from people AS p
join lc on p.person.nbr = lc.person_nbr;
psql:companies-contacted-2025.sql:9: ERROR: missing FROM-clause entry for table "person"
LINE 7: join lc on p.person.nbr = lc.person_nbr;
^
This is obviously a much more complicated query than I expected.
Thanks,
Rich
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Extract only maximum date from column
2025-12-04 19:55 Extract only maximum date from column Rich Shepard <[email protected]>
2025-12-04 20:03 ` Re: Extract only maximum date from column David G. Johnston <[email protected]>
2025-12-04 21:39 ` Re: Extract only maximum date from column Rich Shepard <[email protected]>
2025-12-04 21:58 ` Re: Extract only maximum date from column Adrian Klaver <[email protected]>
2025-12-04 22:17 ` Re: Extract only maximum date from column Rich Shepard <[email protected]>
@ 2025-12-04 22:33 ` Adrian Klaver <[email protected]>
0 siblings, 0 replies; 16+ messages in thread
From: Adrian Klaver @ 2025-12-04 22:33 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; pgsql-general
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)
> select p.person_nbr, p.company_nbr, lc.last_contact
> from people AS p
> join lc on p.person.nbr = lc.person_nbr;
>
> psql:companies-contacted-2025.sql:9: ERROR: missing FROM-clause entry
> for table "person"
> LINE 7: join lc on p.person.nbr = lc.person_nbr;
My mistake should be p.person_nbr
> ^
> This is obviously a much more complicated query than I expected.
>
> Thanks,
>
> Rich
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Extract only maximum date from column
2025-12-04 19:55 Extract only maximum date from column Rich Shepard <[email protected]>
2025-12-04 20:03 ` Re: Extract only maximum date from column David G. Johnston <[email protected]>
2025-12-04 21:39 ` Re: Extract only maximum date from column Rich Shepard <[email protected]>
@ 2025-12-04 22:13 ` David G. Johnston <[email protected]>
2025-12-04 22:26 ` Re: Extract only maximum date from column Rich Shepard <[email protected]>
1 sibling, 1 reply; 16+ messages in thread
From: David G. Johnston @ 2025-12-04 22:13 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; +Cc: pgsql-general
On Thursday, December 4, 2025, Rich Shepard <[email protected]>
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 = c.person_nbr and
> last_contact >= '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’t doing
statistics, you are ranking.
Select person.*, lastcontact.* from person join lateral (select contact.*
from contact where contact.person_id=person.person_id order by
last_contact_date desc limit 1) as lastcontact on true;
David J.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Extract only maximum date from column
2025-12-04 19:55 Extract only maximum date from column Rich Shepard <[email protected]>
2025-12-04 20:03 ` Re: Extract only maximum date from column David G. Johnston <[email protected]>
2025-12-04 21:39 ` Re: Extract only maximum date from column Rich Shepard <[email protected]>
2025-12-04 22:13 ` Re: Extract only maximum date from column David G. Johnston <[email protected]>
@ 2025-12-04 22:26 ` Rich Shepard <[email protected]>
2025-12-04 22:29 ` Re: Extract only maximum date from column David G. Johnston <[email protected]>
0 siblings, 1 reply; 16+ messages in thread
From: Rich Shepard @ 2025-12-04 22:26 UTC (permalink / raw)
To: pgsql-general
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 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 = 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 = 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
^
Regards,
Rich
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Extract only maximum date from column
2025-12-04 19:55 Extract only maximum date from column Rich Shepard <[email protected]>
2025-12-04 20:03 ` Re: Extract only maximum date from column David G. Johnston <[email protected]>
2025-12-04 21:39 ` Re: Extract only maximum date from column Rich Shepard <[email protected]>
2025-12-04 22:13 ` Re: Extract only maximum date from column David G. Johnston <[email protected]>
2025-12-04 22:26 ` Re: Extract only maximum date from column Rich Shepard <[email protected]>
@ 2025-12-04 22:29 ` David G. Johnston <[email protected]>
2025-12-05 13:41 ` Re: Extract only maximum date from column Rich Shepard <[email protected]>
0 siblings, 1 reply; 16+ messages in thread
From: David G. Johnston @ 2025-12-04 22:29 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; +Cc: pgsql-general
On Thursday, December 4, 2025, Rich Shepard <[email protected]>
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 contact.*
>> from contact where contact.person_id=person.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 = 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 = 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…
David J.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Extract only maximum date from column
2025-12-04 19:55 Extract only maximum date from column Rich Shepard <[email protected]>
2025-12-04 20:03 ` Re: Extract only maximum date from column David G. Johnston <[email protected]>
2025-12-04 21:39 ` Re: Extract only maximum date from column Rich Shepard <[email protected]>
2025-12-04 22:13 ` Re: Extract only maximum date from column David G. Johnston <[email protected]>
2025-12-04 22:26 ` Re: Extract only maximum date from column Rich Shepard <[email protected]>
2025-12-04 22:29 ` Re: Extract only maximum date from column David G. Johnston <[email protected]>
@ 2025-12-05 13:41 ` Rich Shepard <[email protected]>
0 siblings, 0 replies; 16+ messages in thread
From: Rich Shepard @ 2025-12-05 13:41 UTC (permalink / raw)
To: pgsql-general
On Thu, 4 Dec 2025, David G. Johnston wrote:
>> So:
>> Select person.*, lastcontact.*
>> from people
>> join lateral (select contacts.*
>> from contacts
>> where contacts.person_nbr = people.person_nbr
>> order by last_contact_date
>> desc limit 1)
>> as lastcontact on true;
> I was giving you a query form. You should use the actual table and column
> names in your schema…
David,
The two table names are 'people' and 'contacts', the columns are
'person_nbr' and 'last_contact.' I usually use aliases rather than the table
name to reference the column.
Rich
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Extract only maximum date from column
2025-12-04 19:55 Extract only maximum date from column Rich Shepard <[email protected]>
@ 2025-12-04 22:58 ` Alban Hertroys <[email protected]>
4 siblings, 0 replies; 16+ messages in thread
From: Alban Hertroys @ 2025-12-04 22:58 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; +Cc: pgsql-general
> On 4 Dec 2025, at 20:55, Rich Shepard <[email protected]> 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
That looks like a classical case for a correlated subquery with WHERE NOT EXISTS.
Something like:
select p.person_nbr, p.company_nbr, c.next_contact
from people as p
join contacts as c
on -- I’m really missing some kind of relation between p and c here, I think that’s relevant
where c.next_contact >= ‘2025-11-01’
-- make sure there’s no later contact
and not exists (
select 1 -- the value is irrelevant, as long as there’s no later instance of a contact
from contacts c2
where c2.next_contact >= ‘2025-11-01’
and c2.next_contact > c.next_contact
)
P.S. My mail-client tried to outsmart me in this reply (in no small part) and I just got back from the pub, so I can’t exactly guarantee correctness of the above, but the principal idea should be solid.
Alban Hertroys
--
There is always an exception to always.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Extract only maximum date from column
2025-12-04 19:55 Extract only maximum date from column Rich Shepard <[email protected]>
@ 2025-12-05 09:35 ` Bernice Southey <[email protected]>
4 siblings, 0 replies; 16+ messages in thread
From: Bernice Southey @ 2025-12-05 09:35 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; +Cc: pgsql-general
Rich Shepard <[email protected]> wrote:
> 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.
DISTINCT ON might be what you're looking for. It's an extremely useful
feature in postgres and well worth understanding. Here's a nice
explanation, that's similar to your case:
https://www.geekytidbits.com/postgres-distinct-on/
select distinct on (p.person_nbr) p.person_nbr, p.company_nbr, c.next_contact
from people as p join contacts as c using (person_nbr)
where c.next_contact >= '2025-11-01' order by p.person_nbr, c.next_contact;
Using the following test data:
create table people(person_nbr, company_nbr) as values (1, 1), (2, 1), (3, 2);
create table contacts(person_nbr, next_contact) as values
(1, '2025-10-31'::date), (1, '2025-11-01'), (1, '2025, 11, 02'),
(3, '2025-11-02'::date), (3, '2025-11-03');
Here's the results:
person_nbr | company_nbr | next_contact
------------+-------------+--------------
1 | 1 | 2025-11-01
3 | 2 | 2025-11-02
DISTINCT ON can use an index matching the ORDER BY.
create index on contacts(person_nbr, next_contact);
Add some data to make the index worth it to the planner:
insert into contacts select i, '2025-11-05'::date + j from
generate_series(4, 100) i, generate_series(1, 100) j;
analyze contacts;
Check the query plan:
explain select distinct on (p.person_nbr) ...
This is what you want to see:
-> Index Only Scan using
contacts_person_nbr_next_contact_idx on contacts c
Index Cond: (next_contact >= '2025-11-01'::date)
Thanks, Bernice
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2025-12-05 13:41 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-12-04 19:55 Extract only maximum date from column Rich Shepard <[email protected]>
2025-12-04 20:01 ` Bryan Sayer <[email protected]>
2025-12-04 20:02 ` Ron Johnson <[email protected]>
2025-12-04 20:18 ` Rich Shepard <[email protected]>
2025-12-04 20:03 ` David G. Johnston <[email protected]>
2025-12-04 20:22 ` Rich Shepard <[email protected]>
2025-12-04 21:39 ` Rich Shepard <[email protected]>
2025-12-04 21:58 ` Adrian Klaver <[email protected]>
2025-12-04 22:17 ` Rich Shepard <[email protected]>
2025-12-04 22:33 ` Adrian Klaver <[email protected]>
2025-12-04 22:13 ` David G. Johnston <[email protected]>
2025-12-04 22:26 ` Rich Shepard <[email protected]>
2025-12-04 22:29 ` David G. Johnston <[email protected]>
2025-12-05 13:41 ` Rich Shepard <[email protected]>
2025-12-04 22:58 ` Alban Hertroys <[email protected]>
2025-12-05 09:35 ` Bernice Southey <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox