agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: Re: Most recent row
Date: Fri, 5 May 2017 10:13:07 +0100
Message-ID: <201705051013.07196.gary.stainburn@ringways.co.uk> (raw)
In-Reply-To: <20170505083220.wxdqm3bpwfo2dgre@hermes.hilbert.loc>
References: <201705050925.04194.gary.stainburn@ringways.co.uk>
<20170505083220.wxdqm3bpwfo2dgre@hermes.hilbert.loc>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
view thread (17+ messages) latest in thread
Message-ID: <201705051013.07196.gary.stainburn@ringways.co.uk>
Permalink: ../201705051013.07196.gary.stainburn@ringways.co.uk/
Also on: postgresql.org/message-id/201705051013.07196.gary.stainburn@ringways.co.uk
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk
Subject: Re: Most recent row
In-Reply-To: <201705051013.07196.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox