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 [cid:imagee69a07.PNG@f4290eba.45ae534e]<http://www.niwa.co.nz> 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<https://www.niwa.co.nz> Facebook<https://www.facebook.com/nzniwa> Twitter<https://twitter.com/niwa_nz> LinkedIn<https://www.linkedin.com/company/niwa> Instagram<https://www.instagram.com/niwa_science> 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 <rshep...@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
