Re: [SQL] Most recent row

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.