the SELECT MIN will involve a fulltable or index scan !
Regards.
DET a écrit dans le message <6v2plm$o8s_at_netaxs.com>...
>
>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 CDT
