Re: getting most recent row efficiently

On Sun, 23 Dec 2001 01:26:07 +0900
I wrote <rk73(at)echna(dot)ne(dot)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