From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Fran Fabrizio <ffabrizio(at)mmrd(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: getting most recent row efficiently |
Date: | 2001-12-17 22:13:13 |
Message-ID: | 20011217140730.N53932-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Command Prompt, Inc. | 2001-12-17 23:02:23 | Practical PostgreSQL moves East! |
Previous Message | SHELTON,MICHAEL (Non-HP-Boise,ex1) | 2001-12-17 22:11:03 | Re: getting most recent row efficiently |