At 06:50 PM 8/12/2004 +0000, you wrote:

>Thanks. I have one more question:
>
>Is there a way to number the results of a SELECT, so that I could do
>something like this:
>
>SELECT FIRST 1 index FROM (
> SELECT index FROM (
> SELECT index FROM foo WHERE foo.field='400'
> )
> ) ;
>
>Or some way so that I could do a search for a specific value, and skip
>until I got to the first one of that value?

The query you have will already do that, since the FIRST 1 operator will
find the first row found by the search criteria.

What you are missing here is an ORDER BY clause. Your current query is
searching a set with no particular cardinality. It doesn't make any sense
to use SELECT FIRST... with unordered sets.

You never search indexes - the optimizer will use an index to speed up a
search (though some indexes can actually make a search slower...)

An example:

SELECT FIRST 1 <LIST OF OUTPUT FIELDS> FROM FOO
WHERE AFIELD = '400'
AND SOMETHING > ?
ORDER BY SOMETHING

will return one row, having AFIELD = '400' and SOMETHING being the lowest
value for SOMETHING in the set that is greater than the value supplied in
the replaceable parameter. Your application controls what is the value
passed to the replaceable parameter, i.e. you read the value of SOMETHING
in the last returned row into a variable, and use this variable as input to
the next call. Or you might want the user to input the bottom value for
SOMETHING and pass that instead.

Sometimes it can be convenient to use a selectable stored procedure to do
this sort of stuff, and avoid SELECT FIRST, which is likely to be slow and
costly on huge tables unless you have very tight search parameters with
good indexes.

./hb