Re: Most recent row

select * from mike_people;
10,'Mike'
20,'Jane'
30,'Seiobhan'

select * from mike_assessments;
300,10,'2017-05-05 14:11:24.885633','C'
400,10,'2017-05-05 14:12:22.650532','B'
500,10,'2017-05-05 14:13:07.722378','A'
600,20,'2017-05-05 14:13:26.115105','B'

select * from
( select p_id,p_name from mike_people ) e1
left join lateral
( select max(as_timestamp) from mike_assessments
where p_id = e1.p_id
group by p_id) e2
on true;

10,'Mike','2017-05-05 14:13:07.722378'
20,'Jane','2017-05-05 14:13:26.115105'
30,'Seiobhan','<NULL>'

On Fri, May 5, 2017 at 1:47 PM, Michael Moore <michaeljmoore(at)gmail(dot)com>
wrote:

> 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
>>
>
>