| Myth of Rownum? [message #372401] |
Fri, 09 February 2001 07:25  |
Gayathri
Messages: 19
Registered: February 2001
|
Junior Member |
|
|
Hai oracle experts, here is a simple but challenging question for u!
What is a rownum! I know the answer, but tell me if it can be used in the following queries.
consider a TABLE named TEST having 10 rows;
1. select * from TEST where rownum=1;
2. slect * from TEST where rownum <4;
3. Select * from TEST where rownum>4;
4. Select * from TEST where rownum = 6;
I understand from books that rownum can be used like above. But excepting the first one, all the queries return "No rows selected", despite having 10 rows. Tell me if it true or what is TRUE?
More about rowum after getting answers for this.
Thanks Experts!
gayathri
|
|
|
|
| Re: Myth of Rownum? [message #372403 is a reply to message #372401] |
Fri, 09 February 2001 08:40  |
Bala
Messages: 205
Registered: November 1999
|
Senior Member |
|
|
Hi,
rownum assignment is a post retrieval operation.
Once you have fetched some rows based on your query then your result set will have the rownum assigned to them.
So you can go from the first row to nth row, but you cannot skip the first row and go to second row...
See.. when the query is being issued, an implicit cursor is opened and rows are fetched one at a time to your client.
When you say
1. select * from TEST where rownum=1;
the following series of actions is happened.
A cursor opened,
first rwo is fetched and rownum 1 is assigned, your condition rownum = 1 checked and it is statisfied, so the first row got displayed.
The second is fetched, your condition is checked, is not satisfied, the row is not being displayed and the cursor is closed.
2.slect * from TEST where rownum <4;
(this should work, correct the typo 'slect' and try)
here cursor opened rows are fetched condition is checked, when it reaches fifth row, it stoped.
3. Select * from TEST where rownum>4;
4. Select * from TEST where rownum = 6;
here the first is fetched, condition checked, not statisfied, nothing displayed.
Bala.
|
|
|
|
| Re: Myth of Rownum? [message #372408 is a reply to message #372401] |
Fri, 09 February 2001 08:56 |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
Rownum is a pseudovariable assigned to the rows retrieved by a query as they are retrieved from the database, ie before any ordering.
Your books are wrong.
1) will return the first row that the query finds.
2) will return the first 3 rows.
3) & 4) will return no rows for the following reason: If you discard every row because it fails to meet the rownum criteria (ie not the 10th row that matches the criteria) you have no rows left.
The first two will return data.
1)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
