Re: Subquery to select max(date) value - Mailing list pgsql-general

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