Re: Getting the first row from a select

Hi there,

I may have missed something the others answers, but the fastest way I know to retrieve a record (espcially the first one) is using an index.

If you set an index on the COMPANY Table (let say Company), the following code should do the trick

'Open the table
set rec=db.openrecordset("Company",dbopentable)

'Tell the engine you seek using that index rec.index="Company"

'Lookup the item
rec.seek ">=", WhateverWasEntered

'check you found one
if not rec.nomatch then

    'BINGO, YOU HAVE THE FIRST ONE AND YOU DID NOT BROWSE THE HUGE TABLE... endif

Hope it helped.

Stephane

DET wrote in message <6v0vgc$7r2_at_netaxs.com>...
>Maybe I'm missing something obvious....
>
>I'm trying to write a VB program that will get the first record from a
 query
>something like:
>
>select * from COMPANY where COMPANY_NAME >= 'whatever was entered';
>
>The user will enter part of a name, and the VB program should retrieve the
>first record >= the string entered. It is *not* a requirement that the
>retrieved record be LIKE 'whatever%' (I'm trying to make this
>general-purpose).
>
>Of course, the COMPANY file may be exceptionally huge, so I don't want to
>just download the entire set of records that matches the above inquiry.
>Intuitively, I would have liked to be able to say:
>
>select * from COMPANY where COMPANY_NAME >= 'whatever' and ROWNUM = 1 order
>by COMPANY_NAME;
>
>but it turns out this doesn't work - ROWNUM is assigned before the sort is
>done.
>
>I'm fairly sure I'm not the first person who's run into this :-) but I'm
 not
>conversant enough with SQL yet to know how to do it. I have some vague idea
>of making it a compound statement using MAX() or MIN() to pull out the
 exact
>ID in the inner statement and using that to pull up an exact match in the
>outer statement, but my head starts to hurt when I try to formulate it.
>
>Any advice or suggestions?
>
>
>
>
>
Received on Mon Oct 05 1998 - 00:00:00 CDT