From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@postgresql.org
Subject: Re: Most recent row
Date: Fri, 5 May 2017 10:44:54 +0100
User-Agent: KMail/1.9.10
References: <201705050925.04194.gary.stainburn@ringways.co.uk>
 <20170505093505.GA5611@depesz.com>
In-Reply-To: <20170505093505.GA5611@depesz.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <201705051044.54259.gary.stainburn@ringways.co.uk>
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

On Friday 05 May 2017 10:35:05 hubert depesz lubaczewski wrote:
> On Fri, May 05, 2017 at 09:25:04AM +0100, Gary Stainburn wrote:
> > This question has been asked a few times, and Google returns a few
> > different answers, but I am interested people's opinions and suggestions
> > for the *best* wat to retrieve the most recent row from a table.
> >
> > My case is:
> >
> > create table people (
> >   p_id  serial primary key,
> >  ......
> > );
> >
> > create table assessments (
> >   p_id	int4 not null references people(p_id),
> >   as_timestamp	timestamp not null,
> >   ......
> > );
> >
> > select p.*, (most recent) a.*
> >   from people p, assessments a
> >   ..
> > ;
>
> How many rows are in people? How many in assessments? Do you really want
> data on all people? Or just some?
>
> Best regards,
>
> depesz

This will be open ended so both datasets will just grow over time.  There are 
720 people records currently, and there should be 6-monthly assessments.  

TBH, I was expecting the dataset to be bigger.

I was looking for a balanced solution, combining performance and SQL 'purity'.

For example, the quickest method is probably to store the most recent 
assessment timestamp in the people row, but then that would be classed as 
redundent data as it is derivable from a related table.

While this is a simple example, it is a real one as it is one that I need to 
implement now. However, I'm also looking for a techniquie that I can apply to 
more complex but basically similar situations.


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql