Re: getting most recent row efficiently

On Mon, 17 Dec 2001 16:30:18 -0500
Fran Fabrizio <ffabrizio(at)mmrd(dot)com> wrote:

> I've got a table that keeps a log of a person's favorites over time
> (what follows is a simplified example)
>
> person_id favorite_color
> 1 red
> 1 blue
> 2 green
> 3 yellow
> 3 purple
>
> I want the set of most recent entries for each person. So assuming the
> order they appear above is the order they were inserted, I want:
>
> person_id favorite_color
> 1 blue
> 2 green
> 3 purple
>
> 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?
>

Yes, there is an interesting, but often shaky, way of using sequences.
You, however, need to be careful of disturbances from other sessions;
otherwise you may get an unexpected result because the sequences have
strong possibility of being incremented or decremented by others.
If your platform is a 7.2beta, using a "CREATE TEMP SEQUENCE" clause
seems to assure an increment of 1 per row. Then again, in case of your
real example with a timestamp, I would think the reliable way is to use
its timestamp in the subselect like Stephan's reply.

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

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

Regards,
Masaru Sugawara