Re: Subquery to select max(date) value [RESOLVED]

On Fri, 29 Mar 2019, Andrew Gierth wrote:

> That query seems correct assuming you want the result in descending order
> of next_contact. How did the actual result differ from your expectation?

Andrew,

User error: I had a couple of date typos (2018 rather than 2019) and was
thinking of generating the display in ascending, rather than decending,
order.

Thinking again about what I need in terms of query results I made a couple
of changes. Now the query selects the most recent next_contact date >= a
week ago today and into the future:

select p.person_id, p.lname, p.fname, p.direct_phone, p.active, o.org_name, sq.*
from people as p
join organizations as o on p.org_id = o.org_id
cross join
lateral
(select a.next_contact
from activities as a
where a.person_id = p.person_id and
p.active='True' and
a.next_contact >= current_date - interval '7' day and
a.next_contact is not null
order by a.next_contact ASC
limit 1) sq
order by sq.next_contact ASC;

The results are what I need and allow me to not miss a date as long as I run
the query at least once a week.

My thanks to you, David J., and Ken for the valuable lessons.

Best regards,

Rich