Subject Re: [IBO] Most efficient way to fetch next alphabetical row
Author Lester Caine
terriertech wrote:

> I have a table containing names. When a user requests a particular
> name, I want to also fetch the neighboring names in an alphabetical
> sense. Example: viewing SMITH,JOHN; previous: SMITH,JAMES; next:
> The SQL I am using is:
> SELECT fullname FROM mnames WHERE fullname>'SMITH,JOHN' ORDER BY
> fname;
> SELECT fullname FROM mnames WHERE fullname<'SMITH,JOHN' ORDER BY fname
> So I want to fetch the first row from each result set. There is an
> index on the fullname column. The table contains maybe 50K names and
> is accessed over a low-speed connection, so doing a client-based
> 'locate' is unviable.

I think you need to have a look at 'incremental search'. This will do
most of what you want without requiring you to write anything extra.

I use two fields for the names. FORENAME and SURNAME, and just display
them concatenated - SURNAME || ',' || FORENAME
Then I can sort by SURNAME, FORENAME this just ensures that the surnames
are grouped, followed by the forenames.

The grid then displays a number of entries sorted either forward or
backwards, and typing a letter in the incremental search box it takes
you to the nearest match, and only supplys the lines that are needed for
the grid.

Lester Caine
L.S.Caine Electronic Services