I have a table that records time-series data. It has 3 fields:
timestamp, variable name (e.g., temperature, humidity, pressure), and
the variable's value. Is there a quick way to query for the latest
values of each variable?

I think I've done this before with a self-join but self-join queries
are too slow....

Thanks.

-John

On Feb 5, 11:31 pm, jbk <johnb...@gmail.com> wrote:
> I have a table that records time-series data. It has 3 fields:
> timestamp, variable name (e.g., temperature, humidity, pressure), and
> the variable's value. Is there a quick way to query for the latest
> values of each variable?
>
> I think I've done this before with a self-join but self-join queries
> are too slow....
>
> Thanks.
>
> -John

Appropriately indexed self-join queries are the fastest way I know.

SELECT t1.*
FROM my_table t1
LEFT JOIN my_table t2
ON t1.id = t2.id
AND t1.date < t2.date
WHERE t2.id IS NULL