Re: getting most recent row efficiently

On Sun, 23 Dec 2001 01:26:07 +0900
I wrote <rk73@echna.ne.jp>:


> > Is there any way to retrieve "the most recent row for each distinct
> > person_id?" without making a timestamp column for when the row was
> > inserted and then grouping them by person_id?

 snip

> create sequence seq_doctor1;
> create sequence seq_doctor2;
>
> select setval('seq_doctor1',1), setval('seq_doctor2',1);
> select t1.person_id, t1.favorite_color
>   from (select person_id, favorite_color, nextval('seq_doctor1')-1 as n
>           from doctor
>          order by person_id
>        ) as t1 inner join
>        (select person_id, max(nextval('seq_doctor2')-1) as rank
>           from doctor
>          group by person_id
>        ) as t2 on (t1.n = t2.rank)
> ;


  There also seems to be another way:

  select t1.person_id, t1.favorite_color
    from (select person_id, favorite_color, oid as n
              from doctor
            ) as t1 inner join
           (select person_id, max(oid) as rank
              from doctor
             group by person_id
           ) as t2 on (t1.n = t2.rank)
;

 person_id | favorite_color
-----------+----------------
         1 | blue
         2 | green
         3 | purple
(3 rows)

Regards,
Masaru Sugawara