Silvela, Jaime (Exchange) wrote:
The obvoious way to get the latest measurement of type A would be to
join the table against
SELECT object_id, object_val_type_id, max(observation_date)
FROM object_val
GROUP BY object_id, object_val_type_id
I'm not sure this is actually the result you want; doesn't this give you
all the unique (object_id, object_val_type_id)'s combined with the max
observation_date in the table (as in, not necessarily related to the
records listed)?
I'd think you want this:
SELECT object_id, object_val_type_id, observation_date
FROM object_val
GROUP BY object_id, object_val_type_id, observation_date
HAVING observation_date = max(observation_date)
Which'd return a single record with the highest observation_date. Though
not strictly necessary, I can imagine you'd want observation_date to be
unique, or you could get grouped observations with the same date.
Although ordering and limiting is probably faster.
I don't think the planner is intelligent enough to know that this would
only return the record with the highest observation_date - it may be
smart enough to reject ("drop from the result set") found records after
finding ones with a higher observation_date (which'd be "interesting"
when using cursors) or something along those lines. Hmm... Now I'm all
curious; an EXPLAIN'd be interesting...
Sorry for the mostly useless post :P
Regards,
--
Alban Hertroys
[EMAIL PROTECTED]
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
