hi, we are using oracle 10g, here is the wierd thing i don't
understand, i do "select rownum, name from admin.test", i can see the
result value of rownum is 1 and 2, and both columns has data in it, but
when i do "select name from admin.test where rownum=2", i can't get the
data back, i do "select name from admin.test where rownum=1", the data
is returned, can someone help me? thanks

db2group88@yahoo.com wrote:

> hi, we are using oracle 10g, here is the wierd thing i don't
> understand, i do "select rownum, name from admin.test", i can see the
> result value of rownum is 1 and 2, and both columns has data in it, but
> when i do "select name from admin.test where rownum=2", i can't get the
> data back, i do "select name from admin.test where rownum=1", the data
> is returned, can someone help me? thanks

This is because you have no idea what ROWNUM is.

ROWNUM is not the number of the row in the table.
It is the number of the rows returned to you by your query.
Since there is not ROWNUM = 1 there can not be a 2 or a 43
or a 1000.

Go to http://www.psoug.org
click on Morgan's Library
click on Pseudocolumns
Run the demo

Also visit http://tahiti.oracle.com and look up PSEUDOCOLUMN.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

<db2group88@yahoo.com> wrote in message
news:1104798968.224923.182020@z14g2000cwz.googlegr oups.com...
> hi, we are using oracle 10g, here is the wierd thing i don't
> understand, i do "select rownum, name from admin.test", i can see the
> result value of rownum is 1 and 2, and both columns has data in it, but
> when i do "select name from admin.test where rownum=2", i can't get the
> data back, i do "select name from admin.test where rownum=1", the data
> is returned, can someone help me? thanks
>
When the 1st row is returned its ROWNUM is 1 & 1 <> 2 so it is NOT retained.
When the next row is returned itsROWNUM is 1 (because no previous rows
are/were accepted AND 1 <> 2. etc.....

Have a play around with the following query.

select num, username, rownum from (
select rownum num, username
from dba_users
order by username
)
where num < 10;




<db2group88@yahoo.com> wrote in message
news:1104798968.224923.182020@z14g2000cwz.googlegr oups.com...
> hi, we are using oracle 10g, here is the wierd thing i don't
> understand, i do "select rownum, name from admin.test", i can see the
> result value of rownum is 1 and 2, and both columns has data in it, but
> when i do "select name from admin.test where rownum=2", i can't get the
> data back, i do "select name from admin.test where rownum=1", the data
> is returned, can someone help me? thanks
>

This is expected behavior of the rownum psuedo column going as far back
as I can remember. I do not know where you find this specific fact in
the manuals. In the SQL manual section on pseudo columns where it
convers rownum the manual tells you that any > rownum condition returns
FALSE and shows examples using less than, <, but it does not mention
the fact you can only query for = 1.

The following is from the pl/sql manual (9.2)
>> The value of ROWNUM increases only when a row is retrieved, so the
only meaningful uses of ROWNUM in a WHERE clause are
.... WHERE ROWNUM < constant;
.... WHERE ROWNUM <= constant;
<<

HTH -- Mark D Powell --

On Mon, 03 Jan 2005 16:36:08 -0800, db2group88@yahoo.com wrote:

> hi, we are using oracle 10g, here is the wierd thing i don't
> understand, i do "select rownum, name from admin.test", i can see the
> result value of rownum is 1 and 2, and both columns has data in it, but
> when i do "select name from admin.test where rownum=2", i can't get the
> data back, i do "select name from admin.test where rownum=1", the data
> is returned, can someone help me? thanks

Stated like that, I conclude that

1) you do not understand that rownum is generated dynamically at query
time (and will be different for each query, even if the values seem the
same);

2) you may be reading significance into the value of ROWNUM that doesn't
exist;

3) you may be attempting to extract information incorrectly.

If this is ot just a hypothetical/learning question, you would get the
best answers if you describe what business logic or function you are
trying to accomplish (and provide the table definition).

lol/FGB

"Mark D Powell" <Mark.Powell@eds.com> wrote:
> This is expected behavior of the rownum psuedo column going as far back
> as I can remember. I do not know where you find this specific fact in
> the manuals. In the SQL manual section on pseudo columns where it
> convers rownum the manual tells you that any > rownum condition returns
> FALSE and shows examples using less than, <, but it does not mention
> the fact you can only query for = 1.

I assume you meant any "rownum >" condition returns false (rather than
any "> rownum" condition....). But reality does not look that simple.
A "> rownum" condition can return true in some cases, for example in a
disjunction.


1 select dbms_utility.get_hash_value(owner,0,1000), rownum from
all_objects 2* where dbms_utility.get_hash_value(owner,0,1000)=394 or
rownum > 10 SQL> /

DBMS_UTILITY.GET_HASH_VALUE(OWNER,0,1000) ROWNUM
----------------------------------------- ----------
394 1
394 2
394 3
394 4
394 5
394 6
394 7
394 8
394 9
394 10
394 11
394 12
604 13
604 14
604 15
604 16
604 17
604 18
604 19
604 20
604 21
604 22
.... for many more rows.

Once the left side of the "or" causes more than 10 rows to be returned,
then the right side of the "or", involving a "rownum >" condition, keeps
being true for the rest of the table.


Xho

--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB

so if i want to do a insert into a new table where data is from another
table from row 2 to row 10, what should be right query?

db2group88@yahoo.com wrote:
> so if i want to do a insert into a new table where data is from
another
> table from row 2 to row 10, what should be right query?

insert into mynewtable
select a.col1, a.col2, a.col3, a.col4 from
(select rownum rn, col1, col2, col3, col4 from mytable) a
where a.rn between 2 and 10;

Substitute the proper columns from your source table (all of them) for
the col1, col2, col3, col4 placeholders in BOTH queries.
David Fitzjarrell

fitzjarrell@cox.net wrote:

> db2group88@yahoo.com wrote:
>
>>so if i want to do a insert into a new table where data is from
>
> another
>
>>table from row 2 to row 10, what should be right query?
>
>
> insert into mynewtable
> select a.col1, a.col2, a.col3, a.col4 from
> (select rownum rn, col1, col2, col3, col4 from mytable) a
> where a.rn between 2 and 10;
>
> Substitute the proper columns from your source table (all of them) for
> the col1, col2, col3, col4 placeholders in BOTH queries.
> David Fitzjarrell

Surely this was a classroom assignment.

--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)


----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---