Getting the first row from a select

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 Thu Oct 01 1998 - 00:00:00 CDT