From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk> |
Cc: | "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Most recent row |
Date: | 2017-05-05 15:14:28 |
Message-ID: | CAKFQuwb5ap3=_VvWOAnYG6E4aChk3jQTztube+UW=Kk29V01fQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Fri, May 5, 2017 at 1:25 AM, Gary Stainburn <
gary(dot)stainburn(at)ringways(dot)co(dot)uk> 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
> ..
> ;
>
I would start with something using DISTINCT ON and avoid redundant data.
If performance starts to suck I would then probably add a field to people
where you can record the most recent assessment id and which you would
change via a trigger on assessments.
(not tested)
SELECT DISTINCT ON (p) p, a
FROM people p
LEFT JOIN assessments a USING (p_id)
ORDER BY p, a.as_timestamp DESC;
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2017-05-05 16:02:24 | Re: Most recent row |
Previous Message | hubert depesz lubaczewski | 2017-05-05 11:09:39 | Re: Most recent row |