Hi

I might be off track but I suspect the following would do the trick

SELECT FIRST 1 fullname FROM mnames WHERE fullname>'SMITH,JOHN' ORDER BY
fname;

SELECT FIRST 1 fullname FROM mnames WHERE fullname<'SMITH,JOHN' ORDER BY
fname
DESC;


Rob Martin
Software Engineer

phone +64 03 377 0495
fax +64 03 377 0496
web www.chreos.com

Wild Software Ltd



terriertech wrote:

>
> Dear all,
>
> 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:
> SMITH,PETER
>
> 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
> DESC;
>
> 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.
>
> Here is the problem: In IBX, simply opening two datasets with the
> above SQL and reading the first record works FINE. But when I use
> IBO, it is MUCH slower depending on the position of the name. For
> example, fetching the 'next' name when the current one starts with Z
> is as fast as IBX, but fetching the previous name is horribly slow.
> Converse applies if the current one starts with A.
>
> I am probably doing something wrong or suboptimally, can anyone help
> me? Here is skeleton code.
>
> TIB_Cursor* ibd = new TIB_Cursor(NULL);
> ibt->StartTransaction();
> ibd->IB_Transaction = ibt;
> ibd->IB_Connection = pDatabase;
> ibd->MaxRows = 1;
> ibd->SQL->Clear();
> ibd->SQL->Add([as above]);
> ibd->Prepare();
> ibd->Active = true;
> ibd->First();
> rec_count = 0;
> while (!ibd->Eof && rec_count++ < 1) {
> name = ibd->Fields->Columns[0]->AsString;
> ibd->Next();
> }
> ibd->Active = false;
> etc.
>
> I don't believe this is to do with the server sorting the names, since
> IBX runs fast. I'm open to just about any solution except creating
> generators (I need to do the same thing for many other tables).
>
> Just a side note with the code... the value '1' is actually passed as
> a parameter and is not hard-coded, hence the ibd->Next(). If the 1
> were hard-coded, I know this could be omitted.
>
> Thanks!
>
>
>
>
>
> ___________________________________________________________________________
> IB Objects - direct, complete, custom connectivity to Firebird or
> InterBase
> without the need for BDE, ODBC or any other layer.
> ___________________________________________________________________________
> http://www.ibobjects.com - your IBO community resource for Tech Info
> papers,
> keyword-searchable FAQ, community code contributions and more !
>
>
> *Yahoo! Groups Sponsor*
> ADVERTISEMENT
>
>
> ------------------------------------------------------------------------
> *Yahoo! Groups Links*
>
> * To visit your group on the web, go to:
> http://groups.yahoo.com/group/IBObjects/
>
> * To unsubscribe from this group, send an email to:
> IBObjects-unsubscribe@yahoogroups.com
> <mailto:IBObjects-unsubscribe@yahoogroups.com?subject=Unsubscribe>
>
> * Your use of Yahoo! Groups is subject to the Yahoo! Terms of
> Service <http://docs.yahoo.com/info/terms/>.
>
>