Re: Selecting table row with latest date [RESOLVED] - Mailing list pgsql-general

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