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.