Hi Josef,

first, you need a DESC index on MD(md_machine_point_id,md_timestamp)

select machine_point_id,
( Select first 1 md_value
from MD
where md_machine_point_id = MDP.machine_point_id
and md_timestamp<= :parameter_timestamp
order by md_machine_point_id desc, md_timestamp desc
) as machine_point_last_value

from MDP


Regards.

Ali



--- In firebird-support@yahoogroups.com, "Josef Gschwendtner"
<Josef.Gschwendtner@...> wrote:

>
> 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
>