public inbox for [email protected]help / color / mirror / Atom feed
Extract 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]> 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 20:01 Bryan Sayer <[email protected]> parent: Rich Shepard <[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 20:02 Ron Johnson <[email protected]> parent: 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 20:03 David G. Johnston <[email protected]> parent: 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 20:18 Rich Shepard <[email protected]> parent: Ron Johnson <[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 20:22 Rich Shepard <[email protected]> parent: David G. Johnston <[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 21:39 Rich Shepard <[email protected]> parent: 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 21:58 Adrian Klaver <[email protected]> parent: 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 22:13 David G. Johnston <[email protected]> parent: 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 22:17 Rich Shepard <[email protected]> parent: 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 22:26 Rich Shepard <[email protected]> parent: 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 22:29 David G. Johnston <[email protected]> parent: 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 22:33 Adrian Klaver <[email protected]> parent: Rich Shepard <[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 22:58 Alban Hertroys <[email protected]> parent: Rich Shepard <[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-05 09:35 Bernice Southey <[email protected]> parent: Rich Shepard <[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
* Re: Extract only maximum date from column @ 2025-12-05 13:41 Rich Shepard <[email protected]> parent: David G. Johnston <[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
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