Re: Getting the first row from a select

On Fri, 02 Oct 1998 10:33:05 -0400, Robert Anderson <nospam_at_nospam.com> wrote:
>DET wrote:
>> Maybe I'm missing something obvious....
>> 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.

ROWNUM is, in Oracle, effectively the physical location of the entry. Kind of like your telephone number; just because we just asked for the "B's" doesn't mean that the phone number gets reset to 1...

>SELECT *
>FROM COMPANY
>WHERE COMPANY_NAME >= 'whatever'
>AND ROWNUM < 2
>ORDER BY COMPANY_NAME
>by COMPANY_NAME;

This is still basing the query on the physical row number, which is, if memory serves, assigned at the time the record is inserted.

As such, the physical row number doesn't forcibly bear any relationship to anything semantically useful. The "physical" key is useful if you want to do a query in which you update records in ways that might alter any data-oriented key, thus invalidating a query ordered by that key.

My Oracle reference guide is 8 miles away, so I can't verify what syntax should be used. Possibly something like:

select * from table
where company_name >= 'whatever'
order by company_name
up to 1 rows.

-- 
"What you end up with, after running an operating system concept
through these many marketing coffee filters, is something not unlike
plain hot water."   -- Matt Welsh
cbbrowne_at_ntlug.org- <http//www.hex.net/~cbbrowne/rdbms.html>