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:44:54 +0100
Message-ID: <201705051044.54259.gary.stainburn@ringways.co.uk> (raw)
In-Reply-To: <20170505093505.GA5611@depesz.com>
References: <201705050925.04194.gary.stainburn@ringways.co.uk>
<20170505093505.GA5611@depesz.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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
view thread (17+ messages) latest in thread
Message-ID: <201705051044.54259.gary.stainburn@ringways.co.uk>
Permalink: ../201705051044.54259.gary.stainburn@ringways.co.uk/
Also on: postgresql.org/message-id/201705051044.54259.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: <201705051044.54259.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