How to retrieve third row from any table using "rownum" key word ( i am using oracle-10g)
See Question&Answers more detail:osHow to retrieve third row from any table using "rownum" key word ( i am using oracle-10g)
See Question&Answers more detail:osOracle assigns values to ROWNUM sequentially as rows are produced by the query - thus, the first row fetched gets ROWNUM=1, the second row fetched gets ROWNUM=2, the third row fetched gets ROWNUM=3, etc. Notice - for a row to be assigned ROWNUM=3 two preceding rows MUST be fetched. And this is why your query returns no rows. You're asking the database for the third row fetched - but rows 1 and 2 have never been fetched.
To demonstrate, try running the following queries:
SELECT S.* FROM SALARY S; -- Should return all rows
SELECT ROWNUM, S.* FROM SALARY S; -- Should return all rows with ROWNUM prepended
SELECT ROWNUM, S.* FROM SALARY WHERE ROWNUM=3; -- Should return no rows
To work around your problem, try the following:
SELECT ROW_NUMBER FROM
(SELECT ROWNUM AS ROW_NUMBER, S.* FROM SALARY S)
WHERE ROW_NUMBER = 3;
Share and enjoy.