--- In firebird-support@yahoogroups.com, Paul Vinkenoog <paul@v...> wrote:

> Hi Eyal,
>
> > Assuming bin_table/bin_num hold the bin numbers, and search_bin is
> > the value for which you try to find the best match, try:
> >
> > SELECT MAX(bin_num) FROM bin_table WHERE :search_bin STARTING WITH
> > bin_num
>
> This looks like the simplest and best solution so far!

Thanks. The problem intrigued me so I did give it some thought.

> > I tested it on a few values and it seems to do what you want, but of
> > course you'll have to test more. This is not an efficient query and
> > indexes can't help much if at all, however if you have up to several
> > thousands of records it should be ok.
>
> I think a lex index on bin_num would help a lot, not for the MAX
> (because that will only compare a few values), but for the STARTING
> WITH.

That's what I thought but I tried and even with an index the plan was
still based on natural ordering. This could be due to the fact that my
test table was just a few records so the optimizer chose to ignore the
index.

Anyway for small tables the cost of index storage and I/O would
probably be higher than running the query without an index.

Eyal.