if i fired
select * from table where rownum=1;
it gives me frist row.
select * from table where rownum<=2;
it gives me frist two rows then why
select * from table where rownum=2;
not gives me second row.
it gives no row selected.
please clear me .........
thanks in advance
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.
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.
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