Re: getting most recent row efficiently

On Mon, 17 Dec 2001, Fran Fabrizio wrote:

> monitor-prod=# \d doctor_favorites
>                       Table "doctor_favorites"
>     Attribute    |           Type           |        Modifier
> -----------------+--------------------------+------------------------
>  favorites       | integer                  |
>  remote_ts       | timestamp with time zone | not null
>  local_ts        | timestamp with time zone | not null default now()
>  med_practice_id | integer                  |
>  doctor_id       | integer                  |
> Indices: docid_index,
>          docid_medpracid_index,
>          localts_index,
>          medpracid_index
>
> monitor-test=# \d current_doctor_favorites
>    View "current_doctor_favorites"
>     Attribute    |  Type   | Modifier
> -----------------+---------+----------
>  doctor_id       | integer |
>  med_practice_id | integer |
>  favorites       | integer |
> View definition: SELECT df.doctor_id, df.med_practice_id, df.favorites
> FROM doctor_favorites df WHERE (df.local_ts = (SELECT
> max(doctor_favorites.local_ts) AS max FROM doctor_favorites WHERE
> ((doctor_favorites.doctor_id = df.doctor_id) AND
> (doctor_favorites.med_practice_id = df.med_practice_id))));

Hmm, would something like (untested with this schema)
select df.doctor_id, df.med_practice_id, df.favorites
 from doctor_favorites df,
 (select doctor_id, med_practice_id, max(local_ts) as local_ts from
  doctor_favorites group by doctor_id, med_practice_id) df2
 where df.doctor_id=df2.doctor_id and
       df.med_practice_id=df2.med_practice_id and
       df.local_ts=df2.loca_ts;
potentially be faster?  I'd guess that'd avoid a lot of potential
evaluations.