Re: Subquery to select max(date) value

[Brent Wood <Brent.Wood@xxxxxxxxxx>]

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

Brent Wood

Principal Technician - GIS and Spatial Data Management

Programme Leader - Environmental Information Delivery

T +64-4-386-0529

National Institute of Water & Atmospheric Research Ltd (NIWA)

301 Evans Bay Parade, Greta Point, Wellington

Connect with NIWA: niwa.co.nz Facebook Twitter LinkedIn Instagram

To ensure compliance with legal requirements and to maintain cyber security standards, NIWA's IT systems are subject to ongoing monitoring, activity logging and auditing. This monitoring and auditing service may be provided by third parties. Such third parties can access information transmitted to, processed by and stored on NIWA's IT systems.

________________________________________
From: Rich Shepard <rshepard@xxxxxxxxxxxxxxx>
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