Re: [SQL] Most recent row

On Friday 05 May 2017 09:32:21 Karsten Hilbert 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
> >   ..
> > ;
>
> You will need to provide a definition for *exactly* what
> "most recent" means in this context.
>
> Karsten

Appologies all.  I though that was obvious, but it is only obvious for me.

What I mean by most recent is the assessment record with the highest (most 
recent) timestamp. Specfically join a people row with the  assessment row for 
that people.

Each assessment will assign scores for the person being assessed.  The scores 
from their most recent assessment are their current scores and what I want to 
appear in the view.  In the live project it will actually be a left outer 
join in case the person has not yet been assessed.