Re: Selecting table row with latest date

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