--- In firebird-support@yahoogroups.com, Alexandre Benson Smith
<iblist@...> wrote:

>
> roar4711 wrote:
> > Hi,
> >
> > Im facing a problem where I need to query time series information

from

> > a table stored in a FB 2.1 datbase.
> >
> > The problem is that I need to query one record before and after

the

> > actual period specified in the select statement.
> >
> > Example table:
> >
> > Time Data
> >
> > 1 X
> > 2 X
> > 3 X
> > 4 X
> > 5 X
> >
> >
> > select Data from XY where time>= 2 and time<=4
> >
> > Here I need record with time 1 and 5 also.
> >
> > Easy you might think, but the problem is that the timeserie is not
> > uniform, the table might look like this:
> >
> >
> > Time Data
> >
> > 1 X
> > 5000 X
> > 5100 X
> > 5200 X
> > 5400 X
> > 5700 X
> > 9000 X
> >
> > select Data from XY where time>= 5000 and time<=5700
> >
> > Here I also need record with timestamp 1 and 9000.
> >
> > Any suggestions how to solve the problem is very welcome.
> >
> A quick sugestion.. don't know how it will perform.
>
> select
> Data
> from
> XY
> where
> time >= (select max(time) from XY where Time < 5000) and
> time <= (select min(time) from XY where Time > 5700)
>
> You will need both ASCENDING and DESCENDING indices on XY.TIME.
>
> You will get more than one record on each side if Time could have
> repeated values.
>
> see you !
>
>
> --
> Alexandre Benson Smith
> Development
> THOR Software e Comercial Ltda
> Santo Andre - Sao Paulo - Brazil
> www.thorsoftware.com.br
>

Works like a charm , thank you very much!