Re: getting most recent row efficiently

On Mon, 17 Dec 2001 16:30:18 -0500
Fran Fabrizio <ffabrizio@mmrd.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