Hi Josef,



Here's my suggestion, maybe there are more performant ways but this should
work:



SELECT FIRST 1 DISTINCT(<datapoint>), <timestamp>

FROM <table>

WHERE (<timestamp> <= <timeparam>)

ORDER BY <timestamp> DESC



Of course you have to change the fieldnames and tablename.

The idea of the query is:Give me the first record for each datapoint(only
one) from the table ordered by the time in desc order so that would be the
most recent record.

Maybe it's better without the distinct and with grouping, I'm not sure; I'm
hardly an expert but I thought I'd share my idea.



Franky



From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Josef Gschwendtner
Sent: vrijdag 7 juli 2006 21:15
To: firebird-support@yahoogroups.com
Subject: [firebird-support] get values for a certain timestamp



Dear Firebird users,

does anybody have an idea how to solve the following problem in a clever
fashion?

Our Situation:
==============
We have a database with to tables (amongst others).
In table1 (MDP) are several thousand "machine-datapoints" (e.g
machine-speed or temperature)
For each of these datapoints we get data (values) (e.g. 5 m/s) which is
stored in table2 (MD).

Some datapoints are sending a value each second, some only once a day.
Therefore table MD has many million records. Each record has a foreign
key MDP, the value and the timestamp when the value has been measured.
On the timestamp-field in MD is an ascending index.

Our problem:
============
We need to view the (active) values/states of all datapoints at a
certain time, which is given by the user.

Therefore the challenge is, to find out (as fast as possible) the last
stored value/state of each datapoint before the given timestamp.

Thank you for sharing your knowledge.
Josef Gschwendtner





[Non-text portions of this message have been removed]