Обсуждение: Selecting table row with latest date

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.

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

Rich

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

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 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?

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 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
columnshould 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. 
> Regards,
>
> Rich
>
>

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

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
        ^

On 8/19/21 10:17 AM, Rich Shepard wrote:
> 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
> ...

So is the above the result of the query above or the query I posted below?

Why is there no other information to provide context?

Is it doing what you want?

> 
>> 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
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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

> Yeah, you wrote two from clauses…

David,

Mea culpa! I did. Got that fixed.

Now, this query:
-------
Select distinct on (p.person_nbr) p.person_nbr,
c.contact_date, max(c.next_contact) as next_contac from contacts,
people as p, contacts as c
where c.next_contact <= current_date and
p.person_nbr = c.person_nbr
group by p.person_nbr, c.contact_date
order by p.person_nbr, c.contact_date desc;
-------
results in usable information; e.g.,
  person_nbr | contact_date | next_contact 
------------+--------------+-------------
           1 | 2021-07-21   | 2021-07-26
           4 | 2021-07-28   | 2021-08-16
           6 | 2021-07-28   | 2021-08-16
           9 | 2019-03-26   | 2020-11-17
          11 | 2018-04-05   | 2020-11-17
          13 | 2021-07-28   | 2021-08-16

My thanks to all of you,

Rich

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

> I thought you said (p.person_nbr, c.contact_date) is already unique?

David,

Yes, that's the PK for the contacts table. I'm still unsure what needs to be
explicitly included in a query. Quite often I leave out a column and
postgres tells me it needs to be included in order by or group by.

Rich

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

> Well, in this case I suspect you had made a different mistake which caused
> the error message (probably the max(c.next_contact)) but instead of
> solving the original problem (removing the max(...)) you decided that two
> wrongs (adding or extending a group by) would hopefully make a right
> (which it did, technically). But while the query works it is definitely
> not conceptually correct.

David,

I'm not at all surprised as I use postgres infrenquently. Once I have
queries producing results I need for my business tracking or client data I
just use them. I'm neither a professional DBA nor database developer and use
a variety of technical applications dependent on project needs.

And I'm quite used to being chastised by those who work primarily with a
single application all day every day and I accept that I'm not an expert.

Regards,

Rich

On 8/19/21 3:07 PM, Rich Shepard wrote:
> On Thu, 19 Aug 2021, David G. Johnston wrote:
> 

> David,
> 
> I'm not at all surprised as I use postgres infrenquently. Once I have
> queries producing results I need for my business tracking or client data I
> just use them. I'm neither a professional DBA nor database developer and 
> use
> a variety of technical applications dependent on project needs.
> 
> And I'm quite used to being chastised by those who work primarily with a
> single application all day every day and I accept that I'm not an expert.

Things would go easier if you supplied context with your replies, so:

The exact query that produced a result.

Query results of more then one column so the data itself has context.

Error messages.

Indication of whether the result is what you wanted?

And if it isn't then the expected result.

Then folks could come up with a more complete answer sooner and with 
less frustration for all.


> 
> Regards,
> 
> Rich
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Rich:

On Thu, Aug 19, 2021 at 6:59 PM 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.
> 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.

Is your next_contact really dependent on the contact record (
person+contact_date? ).

I mean, your tables seem CRM like. You stated next_contact for old
contacts is not interesting. It seems next_contact is associated just
by a person, something like this:

You contact person 1 on april 2, decide to follow up on june 2 =>
insert ( p=1, cd=20210402, nc=20210602 ).

Case 1: You contact it in 20210603 , followup for mid july:
   Insert (1, 20210603, 20210715).
   Is the nc for the previous record useful for anything ? ( It seems
to be nor useful for calling, but you may want to do reports to see
next-contact correlation with the contacts on another records )

Case 2: You contact in mid may ( for whatever reason, he calls you,
whatever, you insert a record for follow up in July ( 1,20210515,
20210715).
Is the next contact date for june still valid?

To me, and maybe others, it seems there is only one valid next-contact
date, potentially null, which should go in the person record, and may
be the contacts table should capture the value of next-contact at the
time the contact was made for analysis. This also makes finding
contacts to be made soon easier.

Of course no separate table for next-contact.

Francisco Olarte.

On Fri, 20 Aug 2021, Francisco Olarte wrote:

> Is your next_contact really dependent on the contact record (
> person+contact_date? ).

Francisco,

Yes.

> I mean, your tables seem CRM like. You stated next_contact for old
> contacts is not interesting. It seems next_contact is associated just
> by a person, something like this:

Not true. The history of contacts is highly valuable.

>   Is the nc for the previous record useful for anything ? ( It seems to be
> nor useful for calling, but you may want to do reports to see next-contact
> correlation with the contacts on another records )

Yes.

> To me, and maybe others, it seems there is only one valid next-contact
> date, potentially null, which should go in the person record, and may be
> the contacts table should capture the value of next-contact at the time
> the contact was made for analysis. This also makes finding contacts to be
> made soon easier.

That's because you don't know my business: I'm currently in my twenty-ninth
year as a consultant helping clients avoid and resolve environmental issues
and have long ago found what information I need and use to efficiently run
my business.

Regards,

Rich