Re: Getting the first row from a select

Robert Anderson wrote in message <3614E421.FE5_at_nospam.com>...
>DET wrote:
>>
>> Maybe I'm missing something obvious....
>>
 <SNIP>
>> 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.
>>

 <SNIP>
>> Any advice or suggestions?
>
>This seems to work for me! Try It!
>
>SELECT *
>FROM COMPANY
>WHERE COMPANY_NAME >= 'whatever'
>AND ROWNUM < 2
>ORDER BY COMPANY_NAME
>by COMPANY_NAME;
>

This may work in some DBMS's - but not in Oracle. The ROWNUM is assignd to the selected rows, THEN the selected rows are sorted. You'll get a row that is >= 'whatever', but not necessarily the lowest-valued one that fits.

I girded my loins (what the hell does that mean anyway?) and did some playing yesterday after posting my original message, and I found that this works:

select * from COMPANY where COMPANY_NAME = (select min(COMPANY_NAME) from COMPANY where COMPANY_NAME >= 'whatever');

I don't know how portable it is, but it works on Oracle. I also have no idea how much work the server has to do to come up with this, but the important thing from my point of view is that only one row gets sent across the network. Received on Fri Oct 02 1998 - 00:00:00 CEST