Re: Most recent row

with mytab as (
select p.p_id,a.as_timestamp ,p_name ,grade
from mike_people p, mike_assessments a
where a.p_id = p.p_id)
select p_id, as_timestamp, p_name, grade from mytab z
where not exists( select 1 from mytab x where x.p_id = z.p_id
and z.as_timestamp < x.as_timestamp);
tested

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

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