Re: Select one row

Properly we have a little misunderstanding.

You are right, the row is like a sequence.

What I mean I would like to describe it with a small example (Oracle 7.3x)

I created a sample table and filled it like following:

create table x (feld Varchar2(10));

alter table x add primary key(feld);
insert into x (feld) values ('DEM');
insert into x (feld) values ('USD');
insert into x (feld) values ('ATS');
insert into x (feld) values ('FRF');
insert into x (feld) values ('ARA');
insert into x (feld) values ('GBP');
insert into x (feld) values ('GRD');
insert into x (feld) values ('SEK');

First select: select rownum,feld from x returns the value DEM with the rownum 1

Second select: select rownum,feld from x order by feld returns the value DEM with the rownum 1

Third select: select rownum,feld from x where feld like ('G%') order by feld
returns the value GBP with the rownum 1

Fourth command:
delete from x where feld like 'DEM';
insert into x (feld) values ('DEM');
select rownum,feld from x;
returns USD with rownum 1
value DEM has now 8

Fifth select: select rownum,feld from x where rownum = 2 returns an empty result set

Sixth select: select rownum,feld from x where rownum = 1 returns USD with rownum 1

That is the behaviour of rownum. It won't have wholes in it, but represents the order of inserting records. With a where statement, so that you get a part of the table, you get new rownum's. (Btw realy unique are only the column rowid)

So that was I would like to tell.

Perhaps it helps. Any comments are welcome. Kind regards
Nicolas Bronke

Thor schrieb in Nachricht <72dc78$861$1_at_news.bctel.net>...
>What???
>Rownum is a magic column (iso term for we don't like it!)
>it assigns a sequential number to the row set returned by the SELECT
>statement and has absolutely nothing to do with insert order. If it did it
>would really be breaking the laws of relational calculus!!!
Received on Thu Nov 12 1998 - 06:43:14 CST