Re: [SQL] Most recent row - Mailing list pgsql-sql

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@gmail.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.klaver@aklaver.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.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