Re: Select one row

There is no way to 100% guarantee in Oracle that any given physical retrieval will be the same between 1 select and the next. It all depends on what segments are cached in the SGA db_block_buffers. On further reflection, POSSIBLY if the table can sit in 1 block buffer and is never never never updated.

This means that rownum can change from one select to the next. It might not, but it can.

The only way to get the same retrieval sequence is to sort the logical result set after physical retrieval. But don't sort by rownum, it is assigned at physical retrieval time.

It is possible to get the 'oldest row'/'first row' from a table if and only iff it was either 1-loaded in one block buffer (select for minimum rowid) 2-loaded in sort order with a unique key sorted by that unique key (select min(unique key))

Again, the above presumes the rows have not been modified. Modifying a row may change its physical location (depending on mod, yada yada yada).

In article <72gvuh$366$1_at_news.cowan.edu.au>,   "raji" <no_diggity2_at_yahoo.com> wrote:
> try this.. as an example... if I wanted to put a value at a time from a
> table onto the screen as a message.
>
> cursor temp_cur is
> select field1, field2, field3
> from <tablename>;
>
> temp_var varchar2(255);
>
> temp_rec temp_cur%rowtype
>
> begin
> open temp_cur;
> fetch temp_cur into temp_rec;
> exit when temp_cur%NOTFOUND;
> temp_var := temp_rec.field1;
> close temp_cur;
> end
>
> note:
> a cursor is a constrained select statement, it can hold
>
> select
> from
> where
> order by
>
> Raj! no_diggity2_at_yahoo.com
>
> Kenneth Herskedal wrote in message <364949b1.0_at_d2o204.telia.com>...
> >How can I select only the first row from a table.
> >
> >My select statement returns several rows, but I'm only interested in the
> >first row of the result set.
> >
> >
> >
> >
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Nov 13 1998 - 08:03:04 CST