Re: Subquery to select max(date) value

On Wed, 13 Feb 2019, Brent Wood wrote:

> I have not really followed this thread but would not a query along the lines of
> select * from activity where person_id = n and timestamp = (select
> max(timestamp) from activity where person_id = n);
> give the required answer ie, always return the latest result for the specified person_id??

Brent,

I don't know. What does work is this statement:

SELECT
DISTINCT ON (person_id) person_id,
next_contact
FROM activities AS a
WHERE a.next_contact is not null and a.next_contact <= 'today' and
a.next_contact > '2018-12-31'
ORDER BY person_id,next_contact;

which returns these results:

person_id | next_contact
-----------+--------------
1 | 2019-01-14
4 | 2019-01-14
22 | 2019-01-14
36 | 2019-01-03
37 | 2019-01-14
38 | 2019-01-21
40 | 2019-02-11
41 | 2019-02-11
42 | 2019-02-11
43 | 2019-02-11
44 | 2019-02-11
45 | 2019-02-11
46 | 2019-02-11
(13 rows)

Now I'm learning how to join the people and organization table using LATERAL
join(s) so the results include names and phone numbers.

Thanks for the suggestion,

Rich