Re: Most recent row

with mytab as (
select p.pid,a.as_timestamp
from people p, assessments a
where a.p_id = a.p_id)
select p_id,as_timestamp from mytab z
where not exist( select 1 from mytab x
where x.p_id = z.p_id
and z.as_timestamp > x.as_timestamp);

or you could use analytics

On Fri, May 5, 2017 at 9:02 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 05/05/2017 08:14 AM, David G. Johnston wrote:
>
>> On Fri, May 5, 2017 at 1:25 AM, Gary Stainburn
>> <gary(dot)stainburn(at)ringways(dot)co(dot)uk <mailto: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.
>>
>>
>
> 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(dot)klaver(at)aklaver(dot)com
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>