Re: Subquery to select max(date) value

[Rich Shepard]

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