Selecting table row with latest date

On Thursday, August 19, 2021, Rich Shepard <rshepard@appl-ecosys.com> wrote:

I have a table of contacts (PK is the person_nbr and contact_date)

Select distinct on (person_nbr) ….. order by person_nbr, contact_date desc;

David J.

On Thu, 19 Aug 2021, Rich Shepard wrote:

Please point me to the proper place in the docs where I can learn how to do
this.

If I use DISTINCT ON would this produce the most recent date for each
person_nbr?

Rich

Rich Shepard <rshepard@appl-ecosys.com> writes:

I have a table of contacts (PK is the person_nbr and contact_date) and I
want to select only the row with the latest (most recent) contact_date. The
Comparison Date/Time sections in the postgres 12 doc doesn't appear to have
what I want, and when I try to use the max() aggregate function it throws an
error.

The best way is usually like

select * from mytable order by contact_date desc limit 1;

If you have an index on contact_date this should work very well indeed.

regards, tom lane

On 8/19/21 7:37 AM, Rich Shepard wrote:

I have a table of contacts (PK is the person_nbr and contact_date) and I
want to select only the row with the latest (most recent) contact_date. The
Comparison Date/Time sections in the postgres 12 doc doesn't appear to have
what I want, and when I try to use the max() aggregate function it
throws an
error.

Show the query and the error you received.

Please point me to the proper place in the docs where I can learn how to do
this.

Rich

--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, 19 Aug 2021, David G. Johnston wrote:

Select distinct on (person_nbr) ….. order by person_nbr, contact_date
desc;

David,

Please clarify: would this produce the most recent contact_date for each
person_nbr? The manual reads that two rows (e.g., for the same person_nbr)
are considered distinct if they differ in any one column (e.g.,
contact_dates).

Still confused.

Rich

On Thu, 19 Aug 2021, Tom Lane wrote:

The best way is usually like

select * from mytable order by contact_date desc limit 1;

If you have an index on contact_date this should work very well indeed.

Tom,

I don't have an index on that table. I'll add one and try you suggestion.

Thanks,

Rich

On Thursday, August 19, 2021, Rich Shepard <rshepard@appl-ecosys.com> wrote:

On Thu, 19 Aug 2021, Rich Shepard wrote:

Please point me to the proper place in the docs where I can learn how to do

this.

If I use DISTINCT ON would this produce the most recent date for each
person_nbr?

Yes, I mis-read your request and thought you wanted the most recent per
contact. Frankly, the single most recent is too simple that my mind didn’t
register that possibility.

David J.

On Thu, 19 Aug 2021, Tom Lane wrote:

The best way is usually like
select * from mytable order by contact_date desc limit 1;
If you have an index on contact_date this should work very well indeed.

tom,

I added an index on contact_date and the query returned only one row. Huh!
Not what I expected.

This is the script I need to fine-tune (and I've forgotten the role of sq
since someone suggested it a few years ago):

----------
/* This query selects all whose next_contact date is today or earlier; no nulls.
This version should select the most recent contact_date by person_nbr,
order by person_nbr and next_contact date. STILL NEEDS WORK.
*/

select p.person_nbr, p.lname, p.fname, p.direct_phone, p.cell_phone, o.company_name, sq.*
from people as p
join companies as o on p.company_nbr = o.company_nbr
cross join
lateral
(select *
from contacts as a
where a.person_nbr = p.person_nbr and
a.next_contact <= current_date and
a.next_contact is not null
order by person_nbr, a.next_contact ASC
) sq
order by sq.next_contact ASC;
----------

Rich

On 8/19/21 8:39 AM, Rich Shepard wrote:

On Thu, 19 Aug 2021, Tom Lane wrote:

The best way is usually like
   select * from mytable order by contact_date desc limit 1;
If you have an index on contact_date this should work very well indeed.

tom,

I added an index on contact_date and the query returned only one row. Huh!
Not what I expected.

This is the script I need to fine-tune (and I've forgotten the role of sq
since someone suggested it a few years ago):

----------
/* This query selects all whose next_contact date is today or earlier;
no nulls.
   This version should select the most recent contact_date by person_nbr,
   order by person_nbr and next_contact date. STILL NEEDS WORK.
*/

select p.person_nbr, p.lname, p.fname, p.direct_phone, p.cell_phone,
o.company_name, sq.*
from people as p
     join companies as o on p.company_nbr = o.company_nbr
     cross join
         lateral
         (select *
         from contacts as a
             where a.person_nbr = p.person_nbr and
             a.next_contact <= current_date and
             a.next_contact is not null
         order by person_nbr, a.next_contact ASC
         ) sq
         order by sq.next_contact ASC;
----------

Alright now I am confused. You keep referring to contact_date, yet the
query is referring to next_contact. Are they the same thing, different
things or other?

Rich

--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, 19 Aug 2021, Adrian Klaver wrote:

Alright now I am confused. You keep referring to contact_date, yet the
query is referring to next_contact. Are they the same thing, different
things or other?

Adrian,

The table has 5 columns: person_nbr, contact_date, contact_type, notes, and
next_contact.

I want the query to find all person_nbr whose most recent contact_date has a
next_contact date <= today. I don't need prior contact_dates and their
next_contact dates because some go back several years. I want to know those
I need to contact again based on our most recent contact.

HTH,

Rich

On Aug 19, 2021, at 10:06 AM, Rich Shepard <rshepard@appl-ecosys.com> wrote:

On Thu, 19 Aug 2021, Adrian Klaver wrote:

Alright now I am confused. You keep referring to contact_date, yet the
query is referring to next_contact. Are they the same thing, different
things or other?

Adrian,

The table has 5 columns: person_nbr, contact_date, contact_type, notes, and
next_contact.

I want the query to find all person_nbr whose most recent contact_date has a
next_contact date <= today. I don't need prior contact_dates and their
next_contact dates because some go back several years. I want to know those
I need to contact again based on our most recent contact.

HTH,

Did you try David J’s suggestion? or maybe
select person_nbr, max(next_contact) group by person_nbr where next_contact < now();

A table with person_nbr (pk), next_contact would make this much easier. Seems to me a person can only have one next-contact? (as opposed to all future_contact)

On Thu, 19 Aug 2021, Rob Sargent wrote:

Did you try David J’s suggestion? or maybe

Rob,

Yes.

select person_nbr, max(next_contact) group by person_nbr where
next_contact < now();

A table with person_nbr (pk), next_contact would make this much easier.
Seems to me a person can only have one next-contact? (as opposed to all
future_contact)

The peoples table has person_nbr as the PK. The contacts table has multiple
rows for all contacts with that person_nbr. The contacts table has a
multicolum PK: person_nbr and contact_date as there is only one contact on
any given day for that person.

HTH,

Rich

On Aug 19, 2021, at 10:31 AM, Rich Shepard <rshepard@appl-ecosys.com> wrote:

On Thu, 19 Aug 2021, Rob Sargent wrote:

Did you try David J’s suggestion? or maybe

Rob,

Yes.

select person_nbr, max(next_contact) group by person_nbr where
next_contact < now();

A table with person_nbr (pk), next_contact would make this much easier.
Seems to me a person can only have one next-contact? (as opposed to all
future_contact)

The peoples table has person_nbr as the PK. The contacts table has multiple
rows for all contacts with that person_nbr. The contacts table has a
multicolum PK: person_nbr and contact_date as there is only one contact on
any given day for that person.

HTH,

Rich

Yeah, but my quibble is the the table you described up-thread. Your contact table contains next_contact? I think that column should be normalized out.

Using David’s trick as a CTE should be descent starting point for you ultimate result?

On 8/19/21 9:06 AM, Rich Shepard wrote:

On Thu, 19 Aug 2021, Adrian Klaver wrote:

Alright now I am confused. You keep referring to contact_date, yet the
query is referring to next_contact. Are they the same thing, different
things or other?

Adrian,

The table has 5 columns: person_nbr, contact_date, contact_type, notes, and
next_contact.

I want the query to find all person_nbr whose most recent contact_date
has a
next_contact date <= today. I don't need prior contact_dates and their
next_contact dates because some go back several years. I want to know those
I need to contact again based on our most recent contact.

So take David Johnston's query:

Select distinct on (person_nbr) ….. order by person_nbr, contact_date desc;

and add the next_contact filter:

Select distinct on (person_nbr) * from contacts where next_contact <=
current_date order by person_nbr, contact_date desc;

See if that returns what you want from the contacts table. Then you can
attach the other information to it.

HTH,

Rich

--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, 19 Aug 2021, Rob Sargent wrote:

Yeah, but my quibble is the the table you described up-thread. Your
contact table contains next_contact? I think that column should be
normalized out.

Rob,

Why should I have a separate table with one column: next_contact? The
next_contact date is associated with a person and the date that person was
contacted.

Rich

On Aug 19, 2021, at 10:59 AM, Rich Shepard <rshepard@appl-ecosys.com> wrote:

On Thu, 19 Aug 2021, Rob Sargent wrote:

Yeah, but my quibble is the the table you described up-thread. Your
contact table contains next_contact? I think that column should be
normalized out.

Rob,

Why should I have a separate table with one column: next_contact? The
next_contact date is associated with a person and the date that person was
contacted.

Rich

sorry, wasn’t clear: person_nbr, next_contact
On the premise that there can only be one next date.

On Thu, 19 Aug 2021, Adrian Klaver wrote:

So take David Johnston's query:
Select distinct on (person_nbr) ….. order by person_nbr, contact_date desc;

Adrian,

contact_date
--------------
2021-08-17
2019-05-14
2019-05-15
2021-08-17
2018-04-05
2021-08-17
2018-04-05
2021-07-23
2019-04-01
2019-05-10
2019-03-15
2021-08-17
2019-04-16
2019-05-15
2021-08-17
2019-05-29
2021-08-17
2019-05-29
2021-07-23
2021-08-12
...

and add the next_contact filter:

Select distinct on (person_nbr) * from contacts where next_contact <=
current_date order by person_nbr, contact_date desc;

Select distinct on (p.person_nbr) * from c.contacts
from people as p, contacts as c
where cnext_contact <= current_date and
p.person_nbr = c.person_nbr
order by p.person_nbr, c.contact_date desc;

$ psql -d bustrac -f test_query.sql
psql:test_query.sql:5: ERROR: syntax error at or near "from"
LINE 2: from people as p, contacts as c
^

I still am missing quite a bit.

Rich

On Thu, 19 Aug 2021, Rob Sargent wrote:

sorry, wasn’t clear: person_nbr, next_contact
On the premise that there can only be one next date.

Rob,

I wasn't sufficiently clear. The contacts table has a row for each
(person_nbr, contact_date). For each row there's also either a next_contact
date or that column is null (e.g., pandemic-related no longer at that
company).

Regards,

Rich

On Aug 19, 2021, at 11:20 AM, Rich Shepard <rshepard@appl-ecosys.com> wrote:

On Thu, 19 Aug 2021, Rob Sargent wrote:

sorry, wasn’t clear: person_nbr, next_contact
On the premise that there can only be one next date.

Rob,

I wasn't sufficiently clear. The contacts table has a row for each
(person_nbr, contact_date). For each row there's also either a next_contact
date or that column is null (e.g., pandemic-related no longer at that
company).

Yeah, I get that each person may have been contacted more than once for a history of contacting. I just feel that “next_contact” doesn’t belong there.