agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Adrian Klaver <adrian.klaver@aklaver.com>
To: David G. Johnston <david.g.johnston@gmail.com>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Most recent row
Date: Fri, 5 May 2017 09:02:24 -0700
Message-ID: <bfb00eb6-6e2c-57e4-a28d-dcca96a894f6@aklaver.com> (raw)
In-Reply-To: <CAKFQuwb5ap3=_VvWOAnYG6E4aChk3jQTztube+UW=Kk29V01fQ@mail.gmail.com>
References: <201705050925.04194.gary.stainburn@ringways.co.uk>
<CAKFQuwb5ap3=_VvWOAnYG6E4aChk3jQTztube+UW=Kk29V01fQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
On 05/05/2017 08:14 AM, David G. Johnston wrote:
> On Fri, May 5, 2017 at 1:25 AM, Gary Stainburn
> <gary.stainburn@ringways.co.uk <mailto:gary.stainburn@ringways.co.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.
>
My take:
create table people(p_id SERIAL PRIMARY KEY, name_first VARCHAR,
name_last VARCHAR);
create table assessments(p_id INTEGER NOT NULL REFERENCES people(p_id),
as_timestamp TIMESTAMP NOT NULL)
insert into people(name_first, name_last) values ('Daffy', 'Duck'),
('Mickey', 'Mouse'), ('Rocky', 'Squirrel');
insert into assessments (p_id, as_timestamp) values (1, '09/12/2016'),
(3, '10/01/2016'), (2, '11/14/2016'), (1, '12/27/2016'),
(2,'01/03/2017'),(3, '02/23/2017'), (1, '03/05/2017');
SELECT
*
FROM
people
JOIN (
SELECT
p_id,
max(as_timestamp) AS last_assessment
FROM
assessments
GROUP BY
p_id) AS max_ts ON people.p_id = max_ts.p_id
JOIN assessments AS a ON a.p_id = max_ts.p_id
AND a.as_timestamp = max_ts.last_assessment
ORDER BY
people.p_id;
p_id | name_first | name_last | p_id | max
------+------------+-----------+------+---------------------
1 | Daffy | Duck | 1 | 2017-03-05 00:00:00
2 | Mickey | Mouse | 2 | 2017-01-03 00:00:00
3 | Rocky | Squirrel | 3 | 2017-02-23 00:00:00
(3 rows)
--
Adrian Klaver
adrian.klaver@aklaver.com
--
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: <bfb00eb6-6e2c-57e4-a28d-dcca96a894f6@aklaver.com>
Permalink: ../bfb00eb6-6e2c-57e4-a28d-dcca96a894f6@aklaver.com/
Also on: postgresql.org/message-id/bfb00eb6-6e2c-57e4-a28d-dcca96a894f6@aklaver.com
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: adrian.klaver@aklaver.com, david.g.johnston@gmail.com, gary.stainburn@ringways.co.uk
Subject: Re: Most recent row
In-Reply-To: <bfb00eb6-6e2c-57e4-a28d-dcca96a894f6@aklaver.com>
* 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