Re: Getting the first row from a select

Use the group by feature in you query and the select first for the field that you are checking... (Correct me if im wrong)

DET wrote:
>
> 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 Fri Oct 02 1998 - 00:00:00 CDT