This content has been marked as final. Show 8 replies
instead you can use the ROW_NUMBER - function, e. g.:
FROM (SELECT A, B, C,
ROW_NUMBER() OVER (ORDER BY <columnname> ASC) AS ROW_NUMBER
WHERE ROW_NUMBER = 2;
This only works with database 8i and 9i
Rownum returns a number indicating the order in which Oracle select the row from the table. The first row has rownum 1, the second 2 etc.
With a query like
SELECT * FROM table
WHERE ROWNUM = 2
the first row fetched has rownum 1, and makes the where condition false. The next row, the second, is now the first row and has rownum 1, and makes the where condition false, etc.
thanks monika and jan for reply
If row is fetched then ROWNUM will generate at runtime.
For first row.......ROWNUM is 1
So, ROWNUM > n will fail always due to ROWNUM is always 1.
Here n = 2, 3, 4, 5, 6, ...............etc.
This, as with all the other recent postings to this forum, is off-topic.
It has nothing to do with the subject matter of this forum - using database objects (SQL "classes") in the SQL and PL/SQL languages.
Please make en effort to identify the CORRECT forum and post your question there. You will get a far better response to it, than posting it in an off-topic forum.
--To get the second value returned by the query
SELECT t.*, ROWNUM AS rn
WHERE rn BETWEEN 1 AND 3
1. This post is in the wrong forum.
2. This thread is ancient.
3. Your answer is wrong and you would know that if you had bothered to test it before posting.
As ROWNUM is a Pseudo Column, it generates values at runtime of the Query.
So, that we cannot expect the conditions on ROWNUM using greater than ">" operator and equal to "=" with other than "1".
Edited by: Sailaja P on Apr 10, 2012 8:04 PM