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.