Subject: Re: Most recent row
To: "David G. Johnston" <david.g.johnston@gmail.com>,
 Gary Stainburn <gary.stainburn@ringways.co.uk>
References: <201705050925.04194.gary.stainburn@ringways.co.uk>
 <CAKFQuwb5ap3=_VvWOAnYG6E4aChk3jQTztube+UW=Kk29V01fQ@mail.gmail.com>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <bfb00eb6-6e2c-57e4-a28d-dcca96a894f6@aklaver.com>
Date: Fri, 5 May 2017 09:02:24 -0700
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.8.0
MIME-Version: 1.0
In-Reply-To: 
 <CAKFQuwb5ap3=_VvWOAnYG6E4aChk3jQTztube+UW=Kk29V01fQ@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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