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

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 Wood

Programme leader: Environmental Information Delivery
NIWA
DDI:  +64 (4) 3860529

________________________________________
From: Rich Shepard <rshepard@appl-ecosys.com>
Sent: Thursday, February 14, 2019 12:13
To: pgsql-generallists.postgresql.org
Subject: Re: Subquery to select max(date) value

On Wed, 13 Feb 2019, Adrian Klaver wrote:

> Given a sufficiently large date range that may not be true as you may have
> contacted a given person multiple times during that range and generated
> multiple activities records.

Adrian,

This is true as it has occurred. I want only the most recent activity row
associated with that person_id. (NB: while I'm really comfortable with DDL
statements my DML experience is seriously lacking and that's what I need to
improve now.)

I've just read a couple of blog posts on the LATERAL join added in 9.3 and
understand it in theory. Properly applying it to my application is now my
focus (and I need to re-read Andrew's example very closely.)

Best regards,

Rich